SELECT substr(id_path, 2, instr(id_path, '/', 2) - 2) id, COUNT(*) FROM (SELECT t.*, sys_connect_by_path(id, '/') id_path FROM t START WITH parentid = 0 CONNECT BY PRIOR id= parentid) tt WHERE tt.parentid <> 0 GROUP BY substr(id_path, 2, instr(id_path, '/', 2) - 2)
上面写法要求oracle是9i以上版本,10G下大概可以这么写,没测试过,可以试试看 select top_id,count(*) from (SELECT t.*, CONNECT_BY_ROOT id as top_id FROM t START WITH parentid = 0 CONNECT BY PRIOR id= parentid) group by top_id
FROM (SELECT t.*, sys_connect_by_path(id, '/') id_path
FROM t
START WITH parentid = 0
CONNECT BY PRIOR id= parentid) tt
WHERE tt.parentid <> 0
GROUP BY substr(id_path, 2, instr(id_path, '/', 2) - 2)
select top_id,count(*)
from (SELECT t.*, CONNECT_BY_ROOT id as top_id
FROM t
START WITH parentid = 0 CONNECT BY PRIOR id= parentid)
group by top_id