[code=SQL]
-- 树形表
create table treetable
(
tid number primary key,
tvalue varchar2(200),
pid number
);
insert into treetable values(1, 'aa ',0);
insert into treetable values(2, 'bb ',1);
insert into treetable values(3, 'cc ',1);
insert into treetable values(4, 'dd ',1);
insert into treetable values(5, 'ee ',2);
insert into treetable values(6, 'ff ',2);
insert into treetable values(7, 'hh ',3);
insert into treetable values(8, 'hh ',7);
......
---数据表
create table datatable(
did number primary key,
tid number ,---树形id
dataValue number ----要汇总的数据
)
insert into datatable values(2,2,40);
insert into datatable values(3,2,20);
insert into datatable values(4,3,10);
insert into datatable values(5,4,30);
insert into datatable values(6,4,15);
insert into datatable values(7,5,20);
insert into datatable values(8,6,35);
insert into datatable values(9,7,5);
insert into datatable values(10,8,15);
--现在要查询出的结果 ，根据树形ID 1 查出第一层子级 2,3,4
--然后分别根据第一层子级别2,3,4 汇总出他下级的所有数据
--比如 第一层级2 ，那么他的汇总结果就应该是 select sum(dataValue) from datatable t where t.tid in(2,5,6);
--比如 第一层级3 ，那么他的汇总结果就应该是 select sum(dataValue) from datatable t where t.tid in(3,7,8);
--比如 第一层级4 ，那么他的汇总结果就应该是 select sum(dataValue) from datatable t where t.tid in(4);
--输出表格如下：sum_value,和count底下的值我随便写的.
tid sum_value count2
2 60 3
3 30 1
4 70 3
---请各位高手帮忙解决下了。谢谢了。
[/code]
------解决方案--------------------
- SQL code
SQL> SELECT m1.root, 2 SUM(m2.datavalue) sum_value, 3 COUNT(*) cnt 4 FROM (SELECT t.tid, 5 t.pid, 6 CONNECT_BY_ROOT(t.tid) root 7 FROM treetable t 8 START WITH t.pid = 1 9 CONNECT BY PRIOR t.tid = t.pid) m1, 10 datatable m2 11 WHERE m1.tid = m2.tid 12 GROUP BY m1.root 13 ORDER BY m1.root; ROOT SUM_VALUE CNT---------- ---------- ---------- 2 115 4 3 30 3 4 45 2