oracle递归查询子节点个数

Question

现有一张表（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 没法解决个数问题，求教

Answer 1

--建立测试数据
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

Answer 2

问题已解决！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;

Answer 3

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;
测试已经通过，可以解决楼主问题。但是效率问题不敢保证。