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

今天在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
W [:}fh%Z9b0  2  (ITPUB个人空间D.p:c)n"O,V!}dP
  3  SELECT ID, VALUE,
/j8A#P"n;G0  4   (
1[/a1{^ `~
X0  5    SELECT MAX(SYS_CONNECT_BY_PATH(VALUE * (LEVEL - 1), ','))
0zVz_UT0  6    FROM DUAL CONNECT BY ROWNUM <= POWERITPUB个人空间"Q~k-{/o[
  7   ) POWER
:A2e@L6m{/ZU0  8  FROM T
SC|$b|[0  9  )ITPUB个人空间dEM2}9I
 10  SELECT L1, L2, L3, L4, L5ITPUB个人空间XA d*_a^
 11  FROMITPUB个人空间1t"IPe)@:W)K
 12  (
Nl8Ne^'rQZ0 13   SELECT A.LV L1, B.LV L2, C.LV L3, D.LV L4, E.LV L5,ITPUB个人空间t F-c@ cl
 14    A.VALUE + B.VALUE + C.VALUE + D.VALUE + E.VALUE TOTAL
b/x(b*iA;Kux0 15   FROM
C;IN3dm'K?J#F-mTE0 16   (
k)z+{P%y$e9A a0 17    SELECT LEVEL - 1 LV,ITPUB个人空间(o0ZK}S.O{ `F%v+U X
 18     SUBSTR(POWER,ITPUB个人空间G"Kn/R.mR"a5W0~
 19      INSTR(POWER, ',', 1, ROWNUM) + 1,ITPUB个人空间(^}pP%P7GV
 20      INSTR(POWER || ',', ',', 1, ROWNUM + 1) - INSTR(POWER, ',', 1, ROWNUM) - 1) VALUE
#z?"[1rv9jI z-o0 21    FROM (SELECT * FROM T1 WHERE ID = 1)
rsBT7x`(F_!pN}p0 22    CONNECT BY LEVEL <= LENGTH(POWER) - LENGTH(REPLACE(POWER, ','))
T2Ug#Lf Uaf|0 23   ) A,ITPUB个人空间?opUA:s
 24   (ITPUB个人空间O8C2HL6kr(vOg
 25    SELECT LEVEL - 1 LV,
k7Wo l.aX{0 26     SUBSTR(POWER,
&HS$dP"f q#_b0 27      INSTR(POWER, ',', 1, ROWNUM) + 1,ITPUB个人空间dih-A0cO#Co
 28      INSTR(POWER || ',', ',', 1, ROWNUM + 1) - INSTR(POWER, ',', 1, ROWNUM) - 1) VALUE
T3e`H1_[U Y0 29    FROM (SELECT * FROM T1 WHERE ID = 2)
K&nn]-yI;j0 30    CONNECT BY LEVEL <= LENGTH(POWER) - LENGTH(REPLACE(POWER, ','))
9M;V!RGs0 31   ) B,
*A*??$ZY2~];E0 32   (
(WbzT"EQ!jY0 33    SELECT LEVEL - 1 LV,ITPUB个人空间\&N;R ^j&@
 34     SUBSTR(POWER,
0bR1q ]l b^'^0 35      INSTR(POWER, ',', 1, ROWNUM) + 1,ITPUB个人空间/D-X(DG1E T#n;I
 36      INSTR(POWER || ',', ',', 1, ROWNUM + 1) - INSTR(POWER, ',', 1, ROWNUM) - 1) VALUEITPUB个人空间 mOXMN I
 37    FROM (SELECT * FROM T1 WHERE ID = 3)
;M$sSD5f[)l@E8A0 38    CONNECT BY LEVEL <= LENGTH(POWER) - LENGTH(REPLACE(POWER, ','))ITPUB个人空间+]&]}s@Bi`
 39   ) C,
4I$MwBX/z!d0 40   (
(lW[U2g0p0 41    SELECT LEVEL - 1 LV,ITPUB个人空间K&TF4~YWnJ
 42     SUBSTR(POWER,ITPUB个人空间;QL5f_Z6mH+i
 43      INSTR(POWER, ',', 1, ROWNUM) + 1,
Xp$G)N1IM0 44      INSTR(POWER || ',', ',', 1, ROWNUM + 1) - INSTR(POWER, ',', 1, ROWNUM) - 1) VALUE
?3QlZr?3ma
rT0 45    FROM (SELECT * FROM T1 WHERE ID = 4)ITPUB个人空间jL5~
n9XS;\?;F5Tx
 46    CONNECT BY LEVEL <= LENGTH(POWER) - LENGTH(REPLACE(POWER, ','))ITPUB个人空间 i%P2X2H2`1|*W
 47   ) D,ITPUB个人空间4F[!}xqy;}vF
 48   (
8x8J3|Z2]@l]?0 49    SELECT LEVEL - 1 LV,ITPUB个人空间ID:s{t"x
 50     SUBSTR(POWER,ITPUB个人空间-iL`-G5f
 51      INSTR(POWER, ',', 1, ROWNUM) + 1,ITPUB个人空间CH0azk g
 52      INSTR(POWER || ',', ',', 1, ROWNUM + 1) - INSTR(POWER, ',', 1, ROWNUM) - 1) VALUEITPUB个人空间lS0c*xk.R
 53    FROM (SELECT * FROM T1 WHERE ID = 5)ITPUB个人空间]o:F)Fg4^q
 54    CONNECT BY LEVEL <= LENGTH(POWER) - LENGTH(REPLACE(POWER, ','))ITPUB个人空间A*Fx(P%bdU!h
 55   ) EITPUB个人空间7~-}a@+u-si,p
 56  )ITPUB个人空间-E6awq*||u9vm
 57  WHERE TOTAL = 16;

        L1         L2         L3         L4         L5ITPUB个人空间3d\JR$i+C4b
S(b
---------- ---------- ---------- ---------- ----------ITPUB个人空间h:n B^C6XB
         0          0          2          0          2ITPUB个人空间g v%wH9b#_,EL"u9y
         0          0          3          0          1ITPUB个人空间%YI%t }_&N/U
         1          1          0          1          2ITPUB个人空间FBk&Q"yZ'gOX5P
         1          1          1          1          1
8m"V']:hsy'T0         1          1          2          1          0
6BX{ N*f*J N/E#s0         2          0          1          0          2ITPUB个人空间_~L/K1@#CM
         2          0          2          0          1ITPUB个人空间Q T.cUfM
         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^/G;zN*lG0  2   V_STR VARCHAR2(32767);
Fiu_
vh$@7T FD0  3   V_COUNT NUMBER;ITPUB个人空间9V#YB!WK'M3b*F
  4   C_CURSOR SYS_REFCURSOR;
1L#L id"Id5e0  5  BEGINITPUB个人空间htm{/s^7V9f
  6   SELECT COUNT(*) INTO V_COUNT FROM T;
1lP3t+I/D0  7   V_STR := 'WITH T1 AS
#u dO!g!Mq*]Mf#M0  8  (
P_u$X}0G0  9  SELECT ROWNUM, ID, VALUE,ITPUB个人空间`NJ*n&snN6Q)E+Sk_
 10   (ITPUB个人空间~3R-Z,`3AD1|? E
 11    SELECT MAX(SYS_CONNECT_BY_PATH(VALUE * (LEVEL - 1), '',''))
V)gQs0j0 12    FROM DUAL CONNECT BY ROWNUM <= POWERITPUB个人空间@[ A`q%`)Gf+VRux
 13   ) POWER
Q(d!kPnl:L0 14  FROM TITPUB个人空间;_"y6zE ^1yh
 15  )
q[?hAF6Bv0 16  SELECT ';
1B7qh$m$`PA0 17   FOR I IN 1..V_COUNT LOOPITPUB个人空间0Ki9ZLk4cb4b`
 18    V_STR := V_STR || 'L' || I || ', ';
_?lGW7E d]0 19   END LOOP;ITPUB个人空间IMTQ)V5u
Y
 20   V_STR := SUBSTR(V_STR, 1, LENGTH(V_STR) - 2) || 'ITPUB个人空间;jwT&vC
 21  FROM
#LQ,q@|0 22  (
xrIii0 23   SELECT ';
Zp5Od"vi5F*p~z0 24   FOR I IN 1..V_COUNT LOOP
J5[k@uWC7v0 25    V_STR := V_STR || 'A' || I || '.LV L' || I || ', ';
x/[3DP(}V0 26   END LOOP;
y)Oo.L^eJz t&^0 27   FOR I IN 1..V_COUNT LOOP
AwAu ~V
JUr0 28    V_STR := V_STR || 'A' || I || '.VALUE + ';ITPUB个人空间u3{Huir
 29   END LOOP;
R I^]`O)Wb?3^'Teq0 30   V_STR := RTRIM(V_STR, '+ ') || ' TOTAL
O
At7uTTz+S
Ei1}0 31  FROMITPUB个人空间 n(YM~/zX#PU
 32  ';
} @_x-Fxm,{0 33   FOR I IN 1..V_COUNT LOOPITPUB个人空间+N TgL&Q
SH
 34    V_STR := V_STR || '(SELECT LEVEL - 1 LV,
O*V6m!q"UyKZa(t0 35   SUBSTR(POWER,ITPUB个人空间sqS/vXKG
 36    INSTR(POWER, '','', 1, ROWNUM) + 1,
|mdD}d0 37    INSTR(POWER || '','', '','', 1, ROWNUM + 1) - INSTR(POWER, '','', 1, ROWNUM) - 1) VALUE
dFt-t3k!y]E_ d0 38  FROM (SELECT * FROM T1 WHERE ID = ' || I || ')
Xfz/b1p9c0 39  CONNECT BY LEVEL <= LENGTH(POWER) - LENGTH(REPLACE(POWER, '',''))) A' || I || ',ITPUB个人空间S.t M?%W+?4i
 40  ';ITPUB个人空间0Cqc*Z9M/el/l+V
 41   END LOOP;ITPUB个人空间1M
h*r*w,Z#h'P
 42   V_STR := SUBSTR(V_STR, 1, LENGTH(V_STR) - 2) || '
qIC4a7w5y0 43  )ITPUB个人空间9@OF.i w Z/jO1`~h
 44  WHERE TOTAL = 16';
-r7w U2|-vG5s0 45   OPEN C_CURSOR FOR V_STR;
lWW:?_N@.t9HM0 46   RETURN C_CURSOR;
!Ek"o0E,a
I0 47  END;
#Ye&~ YV0 48  /

函数已创建。

过程没有太多值得描述的，为了偷懒，SQL结果输出的麻烦，这里使用REF CURSOR作为范围结果，将游标直接输出：

SQL> SELECT F_RESULT FROM DUAL;

F_RESULT
;b@?:gyk9]0--------------------ITPUB个人空间:a-wjSg [
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

        L1         L2         L3         L4         L5ITPUB个人空间gq4]H ah
---------- ---------- ---------- ---------- ----------
8iG ^+V[$Pc0         0          0          2          0          2
(E4DL(?K)D+[0         0          0          3          0          1ITPUB个人空间*g8k5ibF6p0p#I2{D{
         1          1          0          1          2
As P4re/I5zY0         1          1          1          1          1
@c:Kz([.g(M0         1          1          2          1          0ITPUB个人空间 l;S!@9?Uu
         2          0          1          0          2
-qX)C6Z T0         2          0          2          0          1ITPUB个人空间
k"ZI/ox
         2          0          3          0          0

已选择8行。

另外，如果表中的记录数据量不大的话，可以通过下面的方法来简化SQL的写法：

SQL> WITH T1 AS
\X8skr0  2  (
D ]~G @0  3  SELECT DISTINCT T.ID, T.VALUE, LEVEL - 1 LV, T.VALUE * (LEVEL - 1) POWERITPUB个人空间k.hER`)|?k
  4  FROM T
DJ Ri
KV#{M4]0  5  CONNECT BY LEVEL <= T.POWER
;a6L/G%S S2k C+AV0  6  )
egj'k/k"yLe
llq0  7  SELECT L1, L2, L3, L4, L5ITPUB个人空间(f\ y [lb B0Bu!c2j
  8  FROM
NJ@UMih0  9  (
][vH4rn0 10   SELECT A1.LV L1, A2.LV L2, A3.LV L3, A4.LV L4, A5.LV L5,
UC8zt!d0 11    A1.POWER + A2.POWER + A3.POWER + A4.POWER + A5.POWER TOTALITPUB个人空间*VCZ-w5l;WC
 12   FROM
"XmBc&Opr c0 13   (ITPUB个人空间![t~"{#Nr
 14    SELECT *
y*PLT1c0 15    FROM T1ITPUB个人空间4B/YG7}9o w a^R)c8_
 16    WHERE ID = 1
EY p8|b0 17   )A1,ITPUB个人空间\c"lZ*X
 18   (
,[k2BZE)`B"NF*v0 19    SELECT *ITPUB个人空间0F&X] o9^MDu0t
 20    FROM T1ITPUB个人空间 I'T6P1l-vG-W;n(|
 21    WHERE ID = 2ITPUB个人空间Ht{ jM
 22   )A2,ITPUB个人空间)qGKKl W
 23   (
FY ey[J k9xN0 24    SELECT *ITPUB个人空间}y"t#n,o*KG|g
 25    FROM T1
y V{8i3l%H0 26    WHERE ID = 3ITPUB个人空间wC4] a:]5m iM
 27   )A3,ITPUB个人空间O y+G#O~
 28   (ITPUB个人空间v~8|fT+Hw
 29    SELECT *
9t.c*\4dT?.@#Gu S-D`0 30    FROM T1ITPUB个人空间+Q~x5`*FF3hv
 31    WHERE ID = 4
4gdb hB'Q0 32   )A4,
rR M dnxH
k2I.d0 33   (
M'hH)`6J&]:GV+E0 34    SELECT *ITPUB个人空间B}Sk.dZ2C
 35    FROM T1
:`Ol nv$Ux^0 36    WHERE ID = 5ITPUB个人空间Rx?yI
 37   )A5
)m%z*xMi"ux9e0 38  )ITPUB个人空间.^u0w-\Z(yE:J5~
 39  WHERE TOTAL = 16ITPUB个人空间b#Y4[k+U
 40  ;

        L1         L2         L3         L4         L5ITPUB个人空间G5X5}3n.W/J
---------- ---------- ---------- ---------- ----------ITPUB个人空间QoC3Jh$]C#f{T
         2          0          3          0          0
Q\d t#{[Ms
lP0         1          1          2          1          0ITPUB个人空间 }"s(NW G:R
         2          0          2          0          1
&\0q.J7z6o@q0         0          0          3          0          1ITPUB个人空间J;A)Z;Wtk9?zy
         1          1          1          1          1
PKD:M X9W6O`0         2          0          1          0          2
#gJ"hI k+V N"_0         0          0          2          0          2ITPUB个人空间P gR$QF.zRR M)C;e
         1          1          0          1          2

已选择8行。

注意，这里说的是简化而不是优化。WITH语句中如果不加DISTINCT会产生重复的数据，尤其是数据量大的情况。不过数据量小的情况，这种方法的效率并不低。

对应的函数方法：

SQL> CREATE OR REPLACE FUNCTION F_RESULT RETURN SYS_REFCURSOR ASITPUB个人空间bs/@'K-c/Q
  2   V_STR VARCHAR2(32767);
Mv+H']Y0  3   V_COUNT NUMBER;
:I+y8Qv|DR"h9B0  4   C_CURSOR SYS_REFCURSOR;ITPUB个人空间2? ^*bUAQ3_!Y
  5  BEGINITPUB个人空间O6c:a5@(n1I
  6   SELECT COUNT(*) INTO V_COUNT FROM T;
o;yS.m?;~s0  7   V_STR := 'WITH T1 AS
@sBs|3c0  8  (ITPUB个人空间$VNeMN |.a\:h$F
  9  SELECT DISTINCT T.ID, T.VALUE, LEVEL - 1 LV, T.VALUE * (LEVEL - 1) POWER
:iq\S9[0 10  FROM TITPUB个人空间)X8u$e*?Ts
 11  CONNECT BY LEVEL <= T.POWERITPUB个人空间~lS]-T
 12  )ITPUB个人空间'MT0|S1G:J0_,rN
 13  SELECT ';ITPUB个人空间9E F_~?rz1X*~,n|
 14   FOR I IN 1..V_COUNT LOOPITPUB个人空间8D-{s%Xm
 15    V_STR := V_STR || 'L' || I || ', ';ITPUB个人空间{L-}9Lun
 16   END LOOP;
2Y1Ir!ta ^VdFNI0 17   V_STR := SUBSTR(V_STR, 1, LENGTH(V_STR) - 2) || 'ITPUB个人空间8eX4v4|/]
 18  FROM
A Q0o G:uP$]h)?~0 19  (
$W N-X`)R Z;ydo |L0 20   SELECT ';
P?ep1vFIp)d0 21   FOR I IN 1..V_COUNT LOOP
1b)q%EU \qkDO-M0 22    V_STR := V_STR || 'A' || I || '.LV L' || I || ', ';
"e [&N'},}7u?-Zd0j0 23   END LOOP;
!G!X A.@g:V0 24   FOR I IN 1..V_COUNT LOOPITPUB个人空间j`|n6n&[n;A]7L
 25    V_STR := V_STR || 'A' || I || '.POWER + ';ITPUB个人空间#Wb!b$];` ~j7y"H
 26   END LOOP;
Y.K#wbOR A0 27   V_STR := RTRIM(V_STR, '+ ') || ' TOTALITPUB个人空间 W]#z]IeyG
 28  FROM
9TLG/z9?{1a&lS0 29  ';
}am,Tg0Nw0 30   FOR I IN 1..V_COUNT LOOP
)]%X$dM#{
w\0 31    V_STR := V_STR || ' (
.TX-r%vPy3X ZT0 32    SELECT *ITPUB个人空间/yY;p%u7y+Ib
 33    FROM T1
\;j%m'[r W0 34    WHERE ID = ' || I || '
#ce.rB@xI@$^0 35   )A' || I || ',
;h7r#kh8R"d4^0 36  ';ITPUB个人空间4O"q+[x4c
 37   END LOOP;ITPUB个人空间C,d%j3b5?
 38   V_STR := SUBSTR(V_STR, 1, LENGTH(V_STR) - 2) || 'ITPUB个人空间? fU4N_S
 39  )
TB$~%y Kym
j0 40  WHERE TOTAL = 16';ITPUB个人空间7a
S6Dd&cT.L
 41   OPEN C_CURSOR FOR V_STR;ITPUB个人空间;ssRA%\%`q7b(CB
 42   RETURN C_CURSOR;
pjf[z0 43  END;ITPUB个人空间QUx.lWG
 44  /

函数已创建。

SQL> SELECT F_RESULT FROM DUAL;

F_RESULTITPUB个人空间$c `)Ns8^8G9jOY
--------------------ITPUB个人空间0DKssggCb"y
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

        L1         L2         L3         L4         L5
'I:V!sKbm0---------- ---------- ---------- ---------- ----------
%KI0JOV+T6b*P(`0         2          0          3          0          0
3evs8dg
o9~?0         1          1          2          1          0ITPUB个人空间/F(sU,?/Ng$L0pu4J
         2          0          2          0          1ITPUB个人空间\bw\e
         0          0          3          0          1
+zl Ez%Gi.L;ls0         1          1          1          1          1
%Xn|io]DbY0         2          0          1          0          2ITPUB个人空间.feW%G#E+I^
         0          0          2          0          2
ph&p&[i u!A0_0         1          1          0          1          2

已选择8行。