# 一个复杂问题的求解过程（二）

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

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> 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

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

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

• 博文量
1955
• 访问量
10389566