oracle 查询问题

[table]
|id|name|num|parentid|
|1|A|2|0|
|2|B|5|1|
|3|C|4|2|
|4|D|5|3|
|5|E|5|1|
|6|F|5|1|
[/table]

3个回答

[code="sql"]
WITH t AS (
SELECT '1' tid,'A' tname,2 num,'0' parentid FROM DUAL UNION ALL
SELECT '2' tid,'B' tname,5 num,'1' parentid FROM DUAL UNION ALL
SELECT '3' tid,'C' tname,4 num,'2' parentid FROM DUAL UNION ALL
SELECT '4' tid,'D' tname,5 num,'3' parentid FROM DUAL UNION ALL
SELECT '5' tid,'E' tname,5 num,'1' parentid FROM DUAL UNION ALL
SELECT '6' tid,'F' tname,5 num,'1' parentid FROM DUAL
)
SELECT m.root,
SUM(m.num) num
FROM (SELECT t.*,
CONNECT_BY_ROOT(t.tid) root
FROM t
CONNECT BY PRIOR t.tid = t.parentid) m
GROUP BY m.root
ORDER BY m.root

ROOT NUM

2 14
5 5
6 5
[/code]

1[code]
select sum(num) from tblTest t start with parentid=1 connect by prior id=parentid
[/code]

2[code]
select sum(num) from tblTest t start with id=1 connect by prior id=parentid
[/code]

3[code]
select sum(num) from tblTest t start with parentid=1 connect by prior id=parentid and level <2
[code]

3
select sum(num) from tblTest t start with parentid=1 connect by prior id=parentid and level <2