发表于: 2008.07.15 23:27
分类: ORACLE
出处: http://yangtingkun.itpub.net/post/468/466633
---------------------------------------------------------------
上一篇给出了一个SQL实现,不过由于不是很清楚楼主的含义,加上测试数据过于简单,没有将问题完全展现,因此第一篇给出的SQL并不能完全满足需要。这篇根据新的测试数据来构造求解的SQL。
一个树形聚集SQL问题(一):http://yangtingkun.itpub.net/post/468/466388
虽然上一篇给出的SQL能得到正确的结果,但是真正的情况比测试中要复杂很多,树形的分叉并非只可能存在于第一层,而是在任一层都可能包括多个叶节点。
比如插入一条记录:
SQL> SELECT * FROM T_LEVEL;
LEVELS PARENT CHILD PARENT_QTY CHILD_QTY
---------- ---------- ---------- ---------- ----------
1 A B 1 3
2 B C 2 3
3 C D 5 6
4 D E 1 2
1 A Z 1 3
SQL> INSERT INTO T_LEVEL VALUES (4, 'D', 'F', 1, 3);
已创建 1 行。
SQL> SELECT P,
2 SUBSTR(MAX(D), LENGTH(MAX(D))) C,
3 POWER(10, SUM(LOG(10, Q))) Q
4 FROM
5 (
6 SELECT CONNECT_BY_ROOT(PARENT) P,
7 SYS_CONNECT_BY_PATH(CHILD, '/') D,
8 CHILD_QTY/PARENT_QTY Q
9 FROM T_LEVEL
10 START WITH LEVELS = 1
11 CONNECT BY PRIOR CHILD = PARENT
12 )
13 GROUP BY P, SUBSTR(D, 2, 1);
P C Q
---------- ---------- ----------
A F 32.4A Z 3
现在得到的结果显然是不正确的,这里应该得到3条记录,A到E、A到F和A到Z。
虽然SQL得到的结果是不正确的,不过解题的思想并没有变,仍然是通过SUM解决连乘问题,通过构造来获取聚集的GROUP BY字段。
为了简单这个问题,可以将树形查询反过来进行,从所有的叶节点出发,汇总数据到根节点,具体实现SQL如下:
SQL> SELECT
2 SUBSTR(MAX(D), INSTR(MAX(D), '/', -1) + 1) C,
3 P,
4 POWER(10, SUM(LOG(10, Q))) Q
5 FROM
6 (
7 SELECT CONNECT_BY_ROOT(CHILD) P,
8 SYS_CONNECT_BY_PATH(PARENT, '/') D,
9 CHILD_QTY/PARENT_QTY Q
10 FROM T_LEVEL
11 START WITH CHILD IN
12 (
13 SELECT CHILD FROM
14 (
15 SELECT CHILD, CONNECT_BY_ISLEAF LEAF FROM T_LEVEL
16 START WITH LEVELS = 1
17 CONNECT BY PRIOR CHILD = PARENT
18 )
19 WHERE LEAF = 1
20 )
21 CONNECT BY PRIOR PARENT = CHILD
22 )
23 GROUP BY P
24 ;
C P Q
------------------------------ ------------------------------ ----------
A Z 3A E 10.8A F 16.2
这种方式仍然是10g的方法,因为使用了CONNECT_BY_ISLEAF伪列,而这个伪列是10g的新特性,由来标识当前的记录是否是叶节点。
如果在9i中,则需要使用别的方法来实现这个功能,实现的SQL如下:
SQL> SELECT
2 SUBSTR(MAX(D), INSTR(MAX(D), '/', -1) + 1) C,
3 SUBSTR(P, 2, INSTR(P, '/', 1, 2) - 2) P,
4 POWER(10, SUM(LOG(10, Q))) Q
5 FROM
6 (
7 SELECT SYS_CONNECT_BY_PATH(CHILD, '/') || '/' P,
8 SYS_CONNECT_BY_PATH(PARENT, '/') D,
9 CHILD_QTY/PARENT_QTY Q
10 FROM T_LEVEL
11 START WITH CHILD IN
12 (
13 SELECT CHILD FROM
14 (
15 SELECT CHILD,
16 CASE WHEN LEAD(LEVELS) OVER(ORDER BY ID) > LEVELS THEN 0 ELSE 1 END LEAF
17 FROM
18 (
19 SELECT ROWNUM ID, LEVELS, PARENT, CHILD
20 FROM T_LEVEL
21 START WITH LEVELS = 1
22 CONNECT BY PRIOR CHILD = PARENT
23 ORDER SIBLINGS BY LEVELS
24 )
25 )
26 WHERE LEAF = 1
27 )
28 CONNECT BY PRIOR PARENT = CHILD
29 )
30 GROUP BY SUBSTR(P, 2, INSTR(P, '/', 1, 2) - 2);
C P Q
------------------------------ ------------------------------ ----------
A Z 3A E 10.8A F 16.2
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/82387/viewspace-1009652/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/82387/viewspace-1009652/