A表a_id a_parentId a_nameB表b_id a_idinsert into A values(1, null, '顶级');insert into A values(10, 1, '子级1');
insert into A values(11, 1, '子级2');insert into B values(1, 10);
insert into B values(2, 10);
insert into B values(3, 10);
insert into B values(4, 11);
insert into B values(5, 11);
insert into B values(6, 11);
insert into B values(7, 11);A表的级数不固定,可能10下面还有子级
想查询出的就是A表每条记录对应B表的记录数,如果A表该记录存在子记录则要算上子记录对应B表记录数之和,一直递归..结果大致想这样:
a_id 所对应的数量
1 7
10 3
11 4
insert into A values(11, 1, '子级2');insert into B values(1, 10);
insert into B values(2, 10);
insert into B values(3, 10);
insert into B values(4, 11);
insert into B values(5, 11);
insert into B values(6, 11);
insert into B values(7, 11);A表的级数不固定,可能10下面还有子级
想查询出的就是A表每条记录对应B表的记录数,如果A表该记录存在子记录则要算上子记录对应B表记录数之和,一直递归..结果大致想这样:
a_id 所对应的数量
1 7
10 3
11 4
应该要用到:start with 和connect by.
楼主虽然已经解决,不过没有写出解决方案,俺来写一个。
with a as (
select 1 id, null parentid, '顶级' name from dual union
select 10, 1, '子级1' from dual union
select 11, 1, '子级2' from dual ),
b as (
select 1 sn, 10 id from dual union
select 2, 10 from dual union
select 3, 10 from dual union
select 4, 11 from dual union
select 5, 11 from dual union
select 6, 11 from dual union
select 7, 11 from dual)
-- 计算每个root的下属节点的数量和
select rootid,root_name,sum(cn)
from
-- 通过connect by遍历树,获取每节点为root时下面所有节点
(select c.*,connect_by_root(id) rootid,connect_by_root(name) root_name
from
-- 算出表A每条记录对应表B的记录数
(select a.id,a.name,a.parentid,count(b.id) cn
from a,b
where a.id = b.id(+)
group by a.id,a.name,a.parentid) c
connect by parentid = prior id)
group by rootid,root_name;
首先通过第一个子查询C统计出表A中每个节点对应的表B记录数量,这样下面的递归以子查询C为数据源就可以了。 -- 算出表A每条记录对应表B的记录数
(select a.id,a.name,a.parentid,count(b.id) cn
from a,b
where a.id = b.id(+)
group by a.id,a.name,a.parentid) c然后通过递归语法connect by对结果集C进行处理,以每个节点为根进行遍历(所以没有start with子句),假设有N个结点,遍历的结果就是N个树。得到N个树上每个节点的内容,其中包括该节点所属的根节点的名字及ID。 -- 通过connect by遍历树,获取每节点为root时下面所有节点
(select c.*,connect_by_root(id) rootid,connect_by_root(name) root_name
from
-- 算出表A每条记录对应表B的记录数
(select a.id,a.name,a.parentid,count(b.id) cn
from a,b
where a.id = b.id(+)
group by a.id,a.name,a.parentid) c
connect by parentid = prior id)最后,对得到的结果按照根结点进行分组求和,得到的就是我们想要的结果了。-- 计算每个root的下属节点的数量和
select rootid,root_name,sum(cn)
from
-- 通过connect by遍历树,获取每节点为root时下面所有节点
(select c.*,connect_by_root(id) rootid,connect_by_root(name) root_name
from
-- 算出表A每条记录对应表B的记录数
(select a.id,a.name,a.parentid,count(b.id) cn
from a,b
where a.id = b.id(+)
group by a.id,a.name,a.parentid) c
connect by parentid = prior id)
group by rootid,root_name;