oracle递归查询子节点个数

[rowland_taft]

现有一张表（id，NAME ,layer），
如:有几条记录 ID NAME LAYER(id,名称，父节点id)
1, a, 0；
2， b，1；
5， c ，2；
6， d，2；
7, e, 2；
3， f，1；
8， g，3；
9， g，3；
4， g，1；
10， g，4；

现需要查出每个节点下最小子节点个数 like
ID 个数
1, 6 （对应最小子节点5,6,7,8,9,10）
2，3 (对应最小子节点5，6，7)
5，0
6，0
7，0
3，2
8，0
9，0
4，1
10，0
用startwith CONNECT BY 没法解决个数问题，求教

[rowland_taft]

1
/ | \
2 3 4
/ | \ / \ |
5 6 7 8 9 10

现在想得到1下面有5、6、7、8、9、10 个数为6
2下面有5、6、7 个数为3
3下面有8、9 个数为2
4下面有10 个数为1

[hongqi162]

--建立测试数据
CREATE TABLE temp
(
ID NUMBER,
NAME VARCHAR2(100 BYTE),
PID NUMBER DEFAULT 0
);

INSERT INTO temp
select '1','10','0' from dual union all
select '2','11','1' from dual union all
select '3','20','0' from dual union all
select '4','12','1' from dual union all
select '5','121','2' from dual;

--执行查询
declare
cursor cur is select id from temp;
aid int;
fcount int;
begin
open cur;
loop
fetch cur into aid;
exit when cur%notfound;
select count(*)-1 into fcount from temp
start with id=aid
connect by prior id = pid;
dbms_output.put_line( aid||','||fcount );
end loop;
close cur;
end;
--执行结果
1,3
2,1
3,0
4,0
5,0

[hongqi162]

最小子节点？ 我写错了

[rowland_taft]

问题已解决！

SELECT ID,
DECODE(NUM, 1, 0, NUM) -
(SELECT COUNT(*)
FROM TMP_ZK_Y B
WHERE ID IN (SELECT LAYER FROM TMP_ZK_Y)
START WITH A.ID = B.ID
CONNECT BY PRIOR B.ID = B.LAYER)
FROM (SELECT ID,
LAYER,
LEVEL,
(SELECT COUNT(*)
FROM TMP_ZK_Y B
START WITH A.ID = B.ID
CONNECT BY PRIOR B.ID = B.LAYER) NUM
FROM TMP_ZK_Y A) A;

[hb24775179]

SELECT A.*,
(SELECT COUNT(1)
FROM TEMP B
WHERE LEVEL = (SELECT MAX(LEVEL)
FROM TEMP C
START WITH C.ID = A.ID
CONNECT BY PRIOR C.ID = C.PID)
START WITH B.ID = A.ID
CONNECT BY PRIOR B.ID = B.PID)
FROM TEMP A
START WITH ID = 1
CONNECT BY PRIOR ID = PID
ORDER BY LEVEL, 1;
测试已经通过，可以解决楼主问题。但是效率问题不敢保证。

[manbufenglin]

分析函数，理解中。。。。