类型：转载 责任编辑：asp.net 日期：2007/05/23
热门软件下载：
select t.*,(select count(*) from tree start with id=t.id connect by prior id=pid and id!=0) from tree t
ID PID NAME ORDERNUM (SELECTCOUNT(*)FROMTREESTARTWI
1 0 0 根 16
2 1 0 a 1 11
3 2 0 b 3 2
4 3 0 c 2 2
5 4 1 aa 3 8
6 5 1 ab 4 1
7 6 1 ac 1 1
8 7 2 ba 2 1
9 8 3 ca 3 1
10 9 4 aaa 4 4
11 10 4 aab 5 1
12 11 4 aac 3 1
13 12 4 aad 2 1
14 13 9 aaaa 3 1
15 14 9 aaab 2 1
16 15 9 aaac 5 1
我现在想求多个统计，比如sum(pid),sum(ordernum)怎么写呢？
小弟只会这样写
select t.*,(select sum(pid) from tree start with id=t.id connect by prior id=pid and id!=0) pid_sum,(select sum(ordernum) from tree start with id=t.id connect by prior id=pid and id!=0) ordernum_sum from tree t
效率是不是很低呢？
有没有好一点的做法？
谢谢了
是超高难问题吗？无奈项目催得紧，见谅！~
原贴
http://community.csdn.net/Expert/topic/3448/3448087.xml?temp=.599209
总共200分，不够可以再加
网友回答：
try:
select substr(tre,2,instr(tre,/,1,2)) id,count(*) col1,sum(pid) col2,
sun(orderno) col3 from (
select id,pid,level,SYS_CONNECT_BY_PATH(id, /)||/ tre
from tree
start with id <> 0
connect by prior id = pid) t group by substr(tre,2,instr(tre,/,1,2));