一个复杂问题的求解过程（三）
今天在PUB里面看到一个帖子：http://www.itpub.net/thread-949571-1-1.html。问题本身并不复杂，不过想借这个问题简单描述一下求解的思路。
前两篇文章介绍了利用PL/SQL构造动态SQL的方法来解决问题，这里讨论如何使用SQL来实现。
一个复杂问题的求解过程（一）：http://yangtingkun.itpub.net/post/468/456641
一个复杂问题的求解过程（二）：http://yangtingkun.itpub.net/post/468/456695
前面两篇文章通过PL/SQL构造动态SQL的方法来实现，这是由于构造子查询的个数是不确定的。
如果利用SQL来实现，就必须首先解决这个问题。也就是说找到一个方法，在不使用多个表连接的情况下，获取所有ID不同POWER之间组合的可能性的和。
SQL> WITH T1 AS
2 (
3 SELECT DISTINCT T.ID, T.VALUE, LEVEL - 1 LV, T.VALUE * (LEVEL - 1) POWER
4 FROM T
5 CONNECT BY LEVEL <= T.POWER
6 )
7 SELECT * FROM T1;
ID VALUE LV POWER
---------- ---------- ---------- ----------
2 1 1 1
3 4 1 4
3 4 0 0
5 4 0 0
1 2 2 4
5 4 1 4
4 5 0 0
2 1 0 0
1 2 0 0
3 4 3 12
4 5 1 5
3 4 2 8
1 2 1 2
5 4 2 8
已选择14行。
现在得到的结果是展开了加权与VALUE相乘的所有可能性。下面要达到的目的是ID从1到5中，每个ID任选一条记录，并累加POWER的值，最后过滤结果为16的值。
最自然的想法就是多个表连接通过笛卡儿连接来实现，但是没有办法解决表连接数不确定的问题，想来想去，只有再次通过树形查询来实现：
SQL> SELECT ID, SYS_CONNECT_BY_PATH(POWER, ',') POWER_LIST
2 FROM
3 (
4 SELECT DISTINCT T.ID, T.VALUE, LEVEL - 1 LV, T.VALUE * (LEVEL
5 FROM T
6 CONNECT BY LEVEL <= T.POWER
7 )
8 START WITH ID = 1
9 CONNECT BY PRIOR ID + 1 = ID
10 ORDER BY 1, 2;
ID POWER_LIST
---------- ------------------------------
1 ,0
1 ,2
1 ,4
2 ,0,0
2 ,0,1
2 ,2,0
2 ,2,1
2 ,4,0
2 ,4,1
3 ,0,0,0
3 ,0,0,12
3 ,0,0,4
3 ,0,0,8
3 ,0,1,0
3 ,0,1,12
3 ,0,1,4
3 ,0,1,8
3 ,2,0,0
3 ,2,0,12
3 ,2,0,4
3 ,2,0,8
3 ,2,1,0
3 ,2,1,12
3 ,2,1,4
3 ,2,1,8
3 ,4,0,0
3 ,4,0,12
3 ,4,0,4
3 ,4,0,8
3 ,4,1,0
3 ,4,1,12
3 ,4,1,4
3 ,4,1,8
4 ,0,0,0,0
4 ,0,0,0,5
4 ,0,0,12,0
4 ,0,0,12,5
4 ,0,0,4,0
4 ,0,0,4,5
4 ,0,0,8,0
4 ,0,0,8,5
4 ,0,1,0,0
4 ,0,1,0,5
4 ,0,1,12,0
4 ,0,1,12,5
4 ,0,1,4,0
4 ,0,1,4,5
4 ,0,1,8,0
4 ,0,1,8,5
4 ,2,0,0,0
4 ,2,0,0,5
4 ,2,0,12,0
4 ,2,0,12,5
4 ,2,0,4,0
4 ,2,0,4,5
4 ,2,0,8,0
4 ,2,0,8,5
4 ,2,1,0,0
4 ,2,1,0,5
4 ,2,1,12,0
4 ,2,1,12,5
4 ,2,1,4,0
4 ,2,1,4,5
4 ,2,1,8,0
4 ,2,1,8,5
4 ,4,0,0,0
4 ,4,0,0,5
4 ,4,0,12,0
4 ,4,0,12,5
4 ,4,0,4,0
4 ,4,0,4,5
4 ,4,0,8,0
4 ,4,0,8,5
4 ,4,1,0,0
4 ,4,1,0,5
4 ,4,1,12,0
4 ,4,1,12,5
4 ,4,1,4,0
4 ,4,1,4,5
4 ,4,1,8,0
4 ,4,1,8,5
5 ,0,0,0,0,0
5 ,0,0,0,0,4
5 ,0,0,0,0,8
5 ,0,0,0,5,0
5 ,0,0,0,5,4
5 ,0,0,0,5,8
5 ,0,0,12,0,0
5 ,0,0,12,0,4
5 ,0,0,12,0,8
5 ,0,0,12,5,0
5 ,0,0,12,5,4
5 ,0,0,12,5,8
5 ,0,0,4,0,0
5 ,0,0,4,0,4
5 ,0,0,4,0,8
5 ,0,0,4,5,0
5 ,0,0,4,5,4
5 ,0,0,4,5,8
5 ,0,0,8,0,0
5 ,0,0,8,0,4
5 ,0,0,8,0,8
5 ,0,0,8,5,0
5 ,0,0,8,5,4
5 ,0,0,8,5,8
5 ,0,1,0,0,0
5 ,0,1,0,0,4
5 ,0,1,0,0,8
5 ,0,1,0,5,0
5 ,0,1,0,5,4
5 ,0,1,0,5,8
5 ,0,1,12,0,0
5 ,0,1,12,0,4
5 ,0,1,12,0,8
5 ,0,1,12,5,0
5 ,0,1,12,5,4
5 ,0,1,12,5,8
5 ,0,1,4,0,0
5 ,0,1,4,0,4
5 ,0,1,4,0,8
5 ,0,1,4,5,0
5 ,0,1,4,5,4
5 ,0,1,4,5,8
5 ,0,1,8,0,0
5 ,0,1,8,0,4
5 ,0,1,8,0,8
5 ,0,1,8,5,0
5 ,0,1,8,5,4
5 ,0,1,8,5,8
5 ,2,0,0,0,0
5 ,2,0,0,0,4
5 ,2,0,0,0,8
5 ,2,0,0,5,0
5 ,2,0,0,5,4
5 ,2,0,0,5,8
5 ,2,0,12,0,0
5 ,2,0,12,0,4
5 ,2,0,12,0,8
5 ,2,0,12,5,0
5 ,2,0,12,5,4
5 ,2,0,12,5,8
5 ,2,0,4,0,0
5 ,2,0,4,0,4
5 ,2,0,4,0,8
5 ,2,0,4,5,0
5 ,2,0,4,5,4
5 ,2,0,4,5,8
5 ,2,0,8,0,0
5 ,2,0,8,0,4
5 ,2,0,8,0,8
5 ,2,0,8,5,0
5 ,2,0,8,5,4
5 ,2,0,8,5,8
5 ,2,1,0,0,0
5 ,2,1,0,0,4
5 ,2,1,0,0,8
5 ,2,1,0,5,0
5 ,2,1,0,5,4
5 ,2,1,0,5,8
5 ,2,1,12,0,0
5 ,2,1,12,0,4
5 ,2,1,12,0,8
5 ,2,1,12,5,0
5 ,2,1,12,5,4
5 ,2,1,12,5,8
5 ,2,1,4,0,0
5 ,2,1,4,0,4
5 ,2,1,4,0,8
5 ,2,1,4,5,0
5 ,2,1,4,5,4
5 ,2,1,4,5,8
5 ,2,1,8,0,0
5 ,2,1,8,0,4
5 ,2,1,8,0,8
5 ,2,1,8,5,0
5 ,2,1,8,5,4
5 ,2,1,8,5,8
5 ,4,0,0,0,0
5 ,4,0,0,0,4
5 ,4,0,0,0,8
5 ,4,0,0,5,0
5 ,4,0,0,5,4
5 ,4,0,0,5,8
5 ,4,0,12,0,0
5 ,4,0,12,0,4
5 ,4,0,12,0,8
5 ,4,0,12,5,0
5 ,4,0,12,5,4
5 ,4,0,12,5,8
5 ,4,0,4,0,0
5 ,4,0,4,0,4
5 ,4,0,4,0,8
5 ,4,0,4,5,0
5 ,4,0,4,5,4
5 ,4,0,4,5,8
5 ,4,0,8,0,0
5 ,4,0,8,0,4
5 ,4,0,8,0,8
5 ,4,0,8,5,0
5 ,4,0,8,5,4
5 ,4,0,8,5,8
5 ,4,1,0,0,0
5 ,4,1,0,0,4
5 ,4,1,0,0,8
5 ,4,1,0,5,0
5 ,4,1,0,5,4
5 ,4,1,0,5,8
5 ,4,1,12,0,0
5 ,4,1,12,0,4
5 ,4,1,12,0,8
5 ,4,1,12,5,0
5 ,4,1,12,5,4
5 ,4,1,12,5,8
5 ,4,1,4,0,0
5 ,4,1,4,0,4
5 ,4,1,4,0,8
5 ,4,1,4,5,0
5 ,4,1,4,5,4
5 ,4,1,4,5,8
5 ,4,1,8,0,0
5 ,4,1,8,0,4
5 ,4,1,8,0,8
5 ,4,1,8,5,0
5 ,4,1,8,5,4
5 ,4,1,8,5,8
已选择225行。
从上面的查询结果可以看到，ID为5的记录就等价于5个子查询的笛卡儿的结果。不过这种方式的结果合并到一个字符串里。
下面的工作就是计算每个字符串里面数值的和，然后过滤那些等于16的记录。
最终SQL实现如下：
SQL> SELECT LV_LIST
2 FROM
3 (
4 SELECT ROWNUM RID,
5 ID,
6 SYS_CONNECT_BY_PATH(LV, ' ') LV_LIST,
7 SYS_CONNECT_BY_PATH(POWER, ',') POWER_LIST
8 FROM
9 (
10 SELECT DISTINCT T.ID, T.VALUE, LEVEL - 1 LV, T.VALUE * (LEVEL - 1) POWER
11 FROM T
12 CONNECT BY LEVEL <= T.POWER
13 )
14 START WITH ID = 1
15 CONNECT BY PRIOR ID + 1 = ID
16 ) A,
17 (SELECT ROWNUM RN, COUNT(*) OVER() ID FROM T) B
18 WHERE A.ID = B.ID
19 GROUP BY RID, LV_LIST
20 HAVING SUM(SUBSTR(POWER_LIST,
21 INSTR(POWER_LIST, ',', 1, RN) + 1,
22 INSTR(POWER_LIST || ',', ',', 1, RN + 1) - INSTR(POWER_LIST, ',', 1, RN) - 1)) = 16
23 ;
LV_LIST
----------------------------------------------------------------------
2 0 1 0 2
0 0 2 0 2
0 0 3 0 1
2 0 2 0 1
1 1 1 1 1
1 1 0 1 2
1 1 2 1 0
2 0 3 0 0
已选择8行。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/4227/viewspace-201785/ ，如需转载，请注明出处，否则将追究法律责任。
