如何写这个SQL

confiy 2011-09-28 12:35:08

A表

a_id a_parentId a_name

B表

b_id a_id

insert 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

...全文

314 20 打赏收藏转发到动态举报

写回复

用AI写文章

20 条回复

切换为时间正序

请发表友善的回复…

发表回复

一干好几年 2011-10-08

打赏
举报

那我也来mark一下吧。

liuchengbjaccp 2011-10-08

打赏
举报

来蹭分。。。。。。。。。。。。。。

xiaobn_cn 2011-10-08

打赏
举报

[Quote=引用 17 楼 wangwengfeng 的回复:]
16楼不可取哦，有可能级数是多级的，五级十级都有可能呢，递归吧。另外5楼强悍，这种写法我看了十五分钟，没看懂啊。。。。。。
[/Quote]

首先通过第一个子查询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;

wangwengfeng 2011-10-07

打赏
举报

16楼不可取哦，有可能级数是多级的，五级十级都有可能呢，递归吧。另外5楼强悍，这种写法我看了十五分钟，没看懂啊。。。。。。

jwd001 2011-10-03

打赏
举报

select a_id, (select count(*) from B where b_id in(select a_id from A where start with A1.a_id connect by prior a_id=a_parentId)) from A A1

yingzhilian2008 2011-09-30

打赏
举报

树查询

Rotel-刘志东 2011-09-30

打赏
举报

start with connect by

luyun2011 2011-09-30

打赏
举报

LZ厉害，牛人啊

Alessandro_ 2011-09-30

打赏
举报

结贴吧。

我本是朱 2011-09-29

打赏
举报

[Quote=引用 10 楼 coolkisses 的回复:]
已解决，那大家都来蹭分了，呵呵。
[/Quote]
发表于：2011-09-28 00:35:08
回复于：2011-09-28 01:18:31
这速度够快

coolkisses 2011-09-29

打赏
举报

已解决，那大家都来蹭分了，呵呵。

jdsnhan 2011-09-29

打赏
举报

我来蹭分

xiaobn_cn 2011-09-28

打赏
举报

[Quote=引用 2 楼 yixilan 的回复:]
引用 1 楼 confiy 的回复:
已解决

如何解决的？楼主是用的start with 和connect by么？
[/Quote]

楼主虽然已经解决，不过没有写出解决方案，俺来写一个。





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;

dawugui 2011-09-28

打赏
举报

[Quote=引用 1 楼 confiy 的回复:]
已解决
[/Quote]恭喜.
应该要用到:start with 和connect by.

cosio 2011-09-28

打赏
举报

,如果A表该记录存在子记录则要算上子记录对应B表记录数之和







---记录数之和,10 对应3 11 对应4 不懂是什么逻辑?

yixilan 2011-09-28

打赏
举报

[Quote=引用 1 楼 confiy 的回复:]
已解决
[/Quote]
如何解决的？楼主是用的start with 和connect by么？

Ny-6000 2011-09-28

打赏
举报

祝贺.

Gemini Dean 2011-09-28

打赏
举报

[Quote=引用 1 楼 confiy 的回复:]
已解决
[/Quote]

++

分享一下

Kobayashi 2011-09-28

打赏
举报

这么快就搞定了啊。。

confiy 2011-09-28

打赏
举报

已解决

SQL脚本越写越多，总是觉得编写效率太过于低下，这和打字速度无关。在我个人编写SQL脚本时，至少会把SQL的格式排列成易于阅读的，因为其他人会阅读到你的SQL，无论是在程序中或是脚本文件中，良好的排版不仅让人看...

SQL大小写规范和 sql_mode一、SQL大小写规范1、Windows和Linux平台区别2、Linux下大小写规则设置3、SQL编写建议二、sql_mode的合理设置（了解即可,一般不动）1、宽松模式 vs 严格模式2、模式查看和设置一、SQL大小...

日常开发写SQL的时候，尽量养成这个好习惯呀：写完SQL后，用explain分析一下，尤其注意走不走索引。 explain select * from user where userid =10086 or age =18; 2、操作delete或者update语句，加个limit(SQL...

sql脚本是包含一到多个sql命令的sql语句，我们可以将这些sql脚本放在一个文本文件中（我们称之为“sql脚本文件”），然后通过相关的命令执行这个sql脚本文件。基本步骤如下： 1、创建包含sql命令的sql脚本文件文件...

遇到一个数据迁移的问题，由于sql代码量大，我想试着在一个mybatis标签中写多条sql语句的方式实现，但是出现了错误，不过，我们可以通过在访问mysql的url中添加上这个参数即可实现在一个mybatis标签中写多条sql语句...

Oracle

17,091

社区成员

55,238

社区内容

发帖

与我相关

我的任务

社区管理员

加入社区

近7日
近30日
至今

加载中

查看更多榜单

社区公告

暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章