oracle 树形查询函数,树形层次统计，有没有更好的实现？

本帖最后由 stevendba 于 2015-3-27 17:27 编辑

现在要对树形层面进行统计，在oracle 11g上模拟数据为：

create table temp(

rn number,

CARD_ID varchar2(20),

PARENT_CARD_ID varchar2(20),

CARD_LEVEL number,

ORIGINAL_VALUE number

);

insert into temp (RN, CARD_ID, PARENT_CARD_ID, CARD_LEVEL, ORIGINAL_VALUE)

values (1, '4001426625', '4001427341', 3, 0);

insert into temp (RN, CARD_ID, PARENT_CARD_ID, CARD_LEVEL, ORIGINAL_VALUE)

values (2, '4001410843', '4001426625', 2, 0);

insert into temp (RN, CARD_ID, PARENT_CARD_ID, CARD_LEVEL, ORIGINAL_VALUE)

values (3, '4001344409', '4001410843', 1, 0);

insert into temp (RN, CARD_ID, PARENT_CARD_ID, CARD_LEVEL, ORIGINAL_VALUE)

values (4, '08000000442789', '4001344409', 0, 10);

insert into temp (RN, CARD_ID, PARENT_CARD_ID, CARD_LEVEL, ORIGINAL_VALUE)

values (5, '08000015478841', '4001344409', 0, 10);

insert into temp (RN, CARD_ID, PARENT_CARD_ID, CARD_LEVEL, ORIGINAL_VALUE)

values (6, '4001344659', '4001410843', 1, 0);

insert into temp (RN, CARD_ID, PARENT_CARD_ID, CARD_LEVEL, ORIGINAL_VALUE)

values (7, '08000008751361', '4001344659', 0, 20);

insert into temp (RN, CARD_ID, PARENT_CARD_ID, CARD_LEVEL, ORIGINAL_VALUE)

values (8, '08002110060468', '4001344659', 0, 20);

insert into temp (RN, CARD_ID, PARENT_CARD_ID, CARD_LEVEL, ORIGINAL_VALUE)

values (9, '4001422465', '4001426625', 2, 0);

insert into temp (RN, CARD_ID, PARENT_CARD_ID, CARD_LEVEL, ORIGINAL_VALUE)

values (10, '4001344459', '4001422465', 1, 0);

insert into temp (RN, CARD_ID, PARENT_CARD_ID, CARD_LEVEL, ORIGINAL_VALUE)

values (11, '08000002239359', '4001344459', 0, 30);

insert into temp (RN, CARD_ID, PARENT_CARD_ID, CARD_LEVEL, ORIGINAL_VALUE)

values (12, '08004000275299', '4001344459', 0, 30);

insert into temp (RN, CARD_ID, PARENT_CARD_ID, CARD_LEVEL, ORIGINAL_VALUE)

values (13, '08004000286404', '4001344459', 0, 30);

insert into temp (RN, CARD_ID, PARENT_CARD_ID, CARD_LEVEL, ORIGINAL_VALUE)

values (14, '4001344709', '4001422465', 1, 0);

insert into temp (RN, CARD_ID, PARENT_CARD_ID, CARD_LEVEL, ORIGINAL_VALUE)

values (15, '08004000286738', '4001344709', 0, 40);

insert into temp (RN, CARD_ID, PARENT_CARD_ID, CARD_LEVEL, ORIGINAL_VALUE)

values (16, '08004000287885', '4001344709', 0, 40);

insert into temp (RN, CARD_ID, PARENT_CARD_ID, CARD_LEVEL, ORIGINAL_VALUE)

values (17, '08004000296209', '4001344709', 0, 40);

commit;

SQL> select * from temp order by rn;--整个结果集顺序不能变化

RN CARD_ID             PARENT_CARD_ID   CARD_LEVEL ORIGINAL_VALUE

--- ------------------- ---------------- ---------- --------------

1 4001426625          4001427341                30--统计它下面card_level为2的sum，总体感觉有点像收集统计信息的Analyze函数

2 4001410843          4001426625                20 --统计它下面card_level为1的sum

3 4001344409          4001410843                10 --统计下面card_level为0的sum,这里就是20

4 08000000442789      4001344409                0         10

5 08000015478841      4001344409                0         10

6 4001344659          4001410843                10

7 08000008751361      4001344659                0         20

8 08002110060468      4001344659                0         20

9 4001422465          4001426625                20

10 4001344459          4001422465                10

11 08000002239359      4001344459                0         30

12 08004000275299      4001344459                0         30

13 08004000286404      4001344459                0         30

14 4001344709          4001422465                10

15 08004000286738      4001344709                0         40

16 08004000287885      4001344709                0         40

17 08004000296209      4001344709                0         40

现在实现了功能，但觉得有点繁琐，有没有更简洁的SQL，model子句就算了，开发很难理解

with temp1 as(

select root_id,sum(original_value) original_value1 from(

select t.rn,t.original_value, CONNECT_BY_ROOT(t.card_id) root_id

from temp t

start with card_level in (1, 2, 3)

connect by prior t.card_id = t.parent_card_id) c

group by root_id)

select t.rn,t.card_id,t.parent_card_id,t.card_level,

case when t.original_value=0 then t1.original_value1

else t.original_value end original_value

from temp1 t1,temp t where t.card_id = t1.root_id(+)

order by t.rn;

RN CARD_ID           PARENT_CARD_ID    CARD_LEVEL ORIGINAL_VALUE

--- ----------------------------------- ---------- --------------

1 4001426625        4001427341                 3        270

2 4001410843        4001426625                 2         60

3 4001344409        4001410843                 1         20

4 08000000442789    4001344409                 0         10

5 08000015478841    4001344409                 0         10

6 4001344659        4001410843                 1         40

7 08000008751361    4001344659                 0         20

8 08002110060468    4001344659                 0         20

9 4001422465        4001426625                 2        210

10 4001344459        4001422465                 1         90

11 08000002239359    4001344459                 0         30

12 08004000275299    4001344459                 0         30

13 08004000286404    4001344459                 0         30

14 4001344709        4001422465                 1        120

15 08004000286738    4001344709                 0         40

16 08004000287885    4001344709                 0         40

17 08004000296209    4001344709                 0         40