今天在PUB里面看到一个帖子:http://www.itpub.net/thread-949571-1-1.html。问题本身并不复杂,不过想借这个问题简单描述一下求解的思路。
上一篇文章中已经通过硬编码方式的SQL实现,下面通过函数来构造通用解。
一个复杂问题的求解过程(一):http://yangtingkun.itpub.net/post/468/456641
之所以说上一篇文章中给出的SQL是硬编码方式,是由于在写SQL的时候参考了T表中记录条数。
由于构造子查询个数需要和表中的记录数相等,因此这里写SQL的时候构造了5个子查询的连接。而如果表中的记录发生变化,则这个SQL就不正确了,因此这种没有通用性的SQL是不符合要求的。
但是对于SQL来说,很难动态确定表连接的个数。这里最简单的方法就是通过一个PL/SQL来实现。通过PL/SQL根据T表中的记录情况,来动态构造这个查询的SQL语句。
SQL> WITH T1 AS
2 (
3 SELECT ID, VALUE,
4 (
5 SELECT MAX(SYS_CONNECT_BY_PATH(VALUE * (LEVEL - 1), ','))
6 FROM DUAL CONNECT BY ROWNUM <= POWER
7 ) POWER
8 FROM T
9 )
10 SELECT L1, L2, L3, L4, L5
11 FROM
12 (
13 SELECT A.LV L1, B.LV L2, C.LV L3, D.LV L4, E.LV L5,
14 A.VALUE + B.VALUE + C.VALUE + D.VALUE + E.VALUE TOTAL
15 FROM
16 (
17 SELECT LEVEL - 1 LV,
18 SUBSTR(POWER,
19 INSTR(POWER, ',', 1, ROWNUM) + 1,
20 INSTR(POWER || ',', ',', 1, ROWNUM + 1) - INSTR(POWER, ',', 1, ROWNUM) - 1) VALUE
21 FROM (SELECT * FROM T1 WHERE ID = 1)
22 CONNECT BY LEVEL <= LENGTH(POWER) - LENGTH(REPLACE(POWER, ','))
23 ) A,
24 (
25 SELECT LEVEL - 1 LV,
26 SUBSTR(POWER,
27 INSTR(POWER, ',', 1, ROWNUM) + 1,
28 INSTR(POWER || ',', ',', 1, ROWNUM + 1) - INSTR(POWER, ',', 1, ROWNUM) - 1) VALUE
29 FROM (SELECT * FROM T1 WHERE ID = 2)
30 CONNECT BY LEVEL <= LENGTH(POWER) - LENGTH(REPLACE(POWER, ','))
31 ) B,
32 (
33 SELECT LEVEL - 1 LV,
34 SUBSTR(POWER,
35 INSTR(POWER, ',', 1, ROWNUM) + 1,
36 INSTR(POWER || ',', ',', 1, ROWNUM + 1) - INSTR(POWER, ',', 1, ROWNUM) - 1) VALUE
37 FROM (SELECT * FROM T1 WHERE ID = 3)
38 CONNECT BY LEVEL <= LENGTH(POWER) - LENGTH(REPLACE(POWER, ','))
39 ) C,
40 (
41 SELECT LEVEL - 1 LV,
42 SUBSTR(POWER,
43 INSTR(POWER, ',', 1, ROWNUM) + 1,
44 INSTR(POWER || ',', ',', 1, ROWNUM + 1) - INSTR(POWER, ',', 1, ROWNUM) - 1) VALUE
45 FROM (SELECT * FROM T1 WHERE ID = 4)
46 CONNECT BY LEVEL <= LENGTH(POWER) - LENGTH(REPLACE(POWER, ','))
47 ) D,
48 (
49 SELECT LEVEL - 1 LV,
50 SUBSTR(POWER,
51 INSTR(POWER, ',', 1, ROWNUM) + 1,
52 INSTR(POWER || ',', ',', 1, ROWNUM + 1) - INSTR(POWER, ',', 1, ROWNUM) - 1) VALUE
53 FROM (SELECT * FROM T1 WHERE ID = 5)
54 CONNECT BY LEVEL <= LENGTH(POWER) - LENGTH(REPLACE(POWER, ','))
55 ) E
56 )
57 WHERE TOTAL = 16;
L1 L2 L3 L4 L5
---------- ---------- ---------- ---------- ----------
0 0 2 0 2
0 0 3 0 1
1 1 0 1 2
1 1 1 1 1
1 1 2 1 0
2 0 1 0 2
2 0 2 0 1
2 0 3 0 0
已选择8行。
有了这个SQL的原型,构造动态SQL就很简单了。问题的关键是确定哪些部分是动态的,那些部分是不变的。
上面的SQL中子查询的名字A、B、C、D、E,对于构造动态SQL比较麻烦,这里替换为A1、A2、A3、A4和A5。
SQL> CREATE OR REPLACE FUNCTION F_RESULT RETURN SYS_REFCURSOR AS
2 V_STR VARCHAR2(32767);
3 V_COUNT NUMBER;
4 C_CURSOR SYS_REFCURSOR;
5 BEGIN
6 SELECT COUNT(*) INTO V_COUNT FROM T;
7 V_STR := 'WITH T1 AS
8 (
9 SELECT ROWNUM, ID, VALUE,
10 (
11 SELECT MAX(SYS_CONNECT_BY_PATH(VALUE * (LEVEL - 1), '',''))
12 FROM DUAL CONNECT BY ROWNUM <= POWER
13 ) POWER
14 FROM T
15 )
16 SELECT ';
17 FOR I IN 1..V_COUNT LOOP
18 V_STR := V_STR || 'L' || I || ', ';
19 END LOOP;
20 V_STR := SUBSTR(V_STR, 1, LENGTH(V_STR) - 2) || '
21 FROM
22 (
23 SELECT ';
24 FOR I IN 1..V_COUNT LOOP
25 V_STR := V_STR || 'A' || I || '.LV L' || I || ', ';
26 END LOOP;
27 FOR I IN 1..V_COUNT LOOP
28 V_STR := V_STR || 'A' || I || '.VALUE + ';
29 END LOOP;
30 V_STR := RTRIM(V_STR, '+ ') || ' TOTAL
31 FROM
32 ';
33 FOR I IN 1..V_COUNT LOOP
34 V_STR := V_STR || '(SELECT LEVEL - 1 LV,
35 SUBSTR(POWER,
36 INSTR(POWER, '','', 1, ROWNUM) + 1,
37 INSTR(POWER || '','', '','', 1, ROWNUM + 1) - INSTR(POWER, '','', 1, ROWNUM) - 1) VALUE
38 FROM (SELECT * FROM T1 WHERE ID = ' || I || ')
39 CONNECT BY LEVEL <= LENGTH(POWER) - LENGTH(REPLACE(POWER, '',''))) A' || I || ',
40 ';
41 END LOOP;
42 V_STR := SUBSTR(V_STR, 1, LENGTH(V_STR) - 2) || '
43 )
44 WHERE TOTAL = 16';
45 OPEN C_CURSOR FOR V_STR;
46 RETURN C_CURSOR;
47 END;
48 /
函数已创建。
过程没有太多值得描述的,为了偷懒,SQL结果输出的麻烦,这里使用REF CURSOR作为范围结果,将游标直接输出:
SQL> SELECT F_RESULT FROM DUAL;
F_RESULT
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
L1 L2 L3 L4 L5
---------- ---------- ---------- ---------- ----------
0 0 2 0 2
0 0 3 0 1
1 1 0 1 2
1 1 1 1 1
1 1 2 1 0
2 0 1 0 2
2 0 2 0 1
2 0 3 0 0
已选择8行。
另外,如果表中的记录数据量不大的话,可以通过下面的方法来简化SQL的写法:
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 L1, L2, L3, L4, L5
8 FROM
9 (
10 SELECT A1.LV L1, A2.LV L2, A3.LV L3, A4.LV L4, A5.LV L5,
11 A1.POWER + A2.POWER + A3.POWER + A4.POWER + A5.POWER TOTAL
12 FROM
13 (
14 SELECT *
15 FROM T1
16 WHERE ID = 1
17 )A1,
18 (
19 SELECT *
20 FROM T1
21 WHERE ID = 2
22 )A2,
23 (
24 SELECT *
25 FROM T1
26 WHERE ID = 3
27 )A3,
28 (
29 SELECT *
30 FROM T1
31 WHERE ID = 4
32 )A4,
33 (
34 SELECT *
35 FROM T1
36 WHERE ID = 5
37 )A5
38 )
39 WHERE TOTAL = 16
40 ;
L1 L2 L3 L4 L5
---------- ---------- ---------- ---------- ----------
2 0 3 0 0
1 1 2 1 0
2 0 2 0 1
0 0 3 0 1
1 1 1 1 1
2 0 1 0 2
0 0 2 0 2
1 1 0 1 2
已选择8行。
注意,这里说的是简化而不是优化。WITH语句中如果不加DISTINCT会产生重复的数据,尤其是数据量大的情况。不过数据量小的情况,这种方法的效率并不低。
对应的函数方法:
SQL> CREATE OR REPLACE FUNCTION F_RESULT RETURN SYS_REFCURSOR AS
2 V_STR VARCHAR2(32767);
3 V_COUNT NUMBER;
4 C_CURSOR SYS_REFCURSOR;
5 BEGIN
6 SELECT COUNT(*) INTO V_COUNT FROM T;
7 V_STR := 'WITH T1 AS
8 (
9 SELECT DISTINCT T.ID, T.VALUE, LEVEL - 1 LV, T.VALUE * (LEVEL - 1) POWER
10 FROM T
11 CONNECT BY LEVEL <= T.POWER
12 )
13 SELECT ';
14 FOR I IN 1..V_COUNT LOOP
15 V_STR := V_STR || 'L' || I || ', ';
16 END LOOP;
17 V_STR := SUBSTR(V_STR, 1, LENGTH(V_STR) - 2) || '
18 FROM
19 (
20 SELECT ';
21 FOR I IN 1..V_COUNT LOOP
22 V_STR := V_STR || 'A' || I || '.LV L' || I || ', ';
23 END LOOP;
24 FOR I IN 1..V_COUNT LOOP
25 V_STR := V_STR || 'A' || I || '.POWER + ';
26 END LOOP;
27 V_STR := RTRIM(V_STR, '+ ') || ' TOTAL
28 FROM
29 ';
30 FOR I IN 1..V_COUNT LOOP
31 V_STR := V_STR || ' (
32 SELECT *
33 FROM T1
34 WHERE ID = ' || I || '
35 )A' || I || ',
36 ';
37 END LOOP;
38 V_STR := SUBSTR(V_STR, 1, LENGTH(V_STR) - 2) || '
39 )
40 WHERE TOTAL = 16';
41 OPEN C_CURSOR FOR V_STR;
42 RETURN C_CURSOR;
43 END;
44 /
函数已创建。
SQL> SELECT F_RESULT FROM DUAL;
F_RESULT
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
L1 L2 L3 L4 L5
---------- ---------- ---------- ---------- ----------
2 0 3 0 0
1 1 2 1 0
2 0 2 0 1
0 0 3 0 1
1 1 1 1 1
2 0 1 0 2
0 0 2 0 2
1 1 0 1 2
已选择8行。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-201573/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/4227/viewspace-201573/