Oracle 递归统计所有节点下的子节点个数

来源：互联网发布：淘宝搜索黑科技编辑：程序博客网时间：2024/04/30 03:15

/**递归查询

已知，节点下可以挂子节点或者内容；内容下不能挂节点。

要统计每个节点下的内容个数（包含自己的内容和子节点的内容）

*/

/*create table test_category(
cid varchar2(20),
parentid varchar2(20)
);

create table test_category2content(
cateid varchar2(20),
contid varchar2(20)
);

insert into test_category values('1', '0');
insert into test_category values('11', '1');
insert into test_category values('12', '1');----叶子栏目
insert into test_category values('111', '11');----叶子栏目
insert into test_category values('112', '11');

insert into test_category2content values('1', 'X');
insert into test_category2content values('1', 'X');
insert into test_category2content values('11', 'X');
insert into test_category2content values('11', 'X');
insert into test_category2content values('12', 'X');
insert into test_category2content values('12', 'X');
insert into test_category2content values('111', 'X');
insert into test_category2content values('111', 'X');
insert into test_category2content values('111', 'X');

--insert into test_category2content values('2', 'X');
--insert into test_category2content values('21', 'X');
--insert into test_category values('2', '0');
--insert into test_category values('21', '2');
--insert into test_category values('211', '21');----设为叶子栏目
commit;

--1:9
--11:5
--12:2
--111:3
--112:0
*/

----正确的做法
select x.cid
, (
select sum(nvl(t2.amount, 0))--t1.cid, nvl(t2.amount, 0) as amount
from test_category t1 left join
(select cateid, count(1) as amount from test_category2content group by cateid) t2
on t1.cid = t2.cateid
start with t1.cid = x.cid
connect by prior t1.cid = t1.parentid
) as cc
from test_category x
;

----//====稍微研究下connect by的作用

---从parentid到cid开始递归，并以parentid为主展示这条记录
select t1.parentid, t1.cid, level
from test_category t1
start with t1.parentid = 1
connect by prior t1.parentid = t1.cid
;
---从parentid到cid开始递归，并以parentid为主展示这条记录
select t1.parentid, t1.cid, level
from test_category t1
start with t1.cid = 1
connect by prior t1.parentid = t1.cid
;

---从parentid到cid开始递归，并以cid为主展示这条记录
select t1.cid, t1.parentid, level
from test_category t1
start with t1.parentid = 1
connect by t1.parentid = prior t1.cid
;
---从parentid到cid开始递归，并以cid为主展示这条记录
select t1.cid, t1.parentid, level
from test_category t1
start with t1.cid = 1
connect by t1.parentid = prior t1.cid
;

0 0