现有一张表(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 没法解决个数问题,求教
如:有几条记录 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 没法解决个数问题,求教
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
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;
(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;
测试已经通过,可以解决楼主问题。但是效率问题不敢保证。