求助!如何查询子代节点数量?

Question

现有表如下   id  pid  name
   a    c   ddd      
   b    c   ddd      
   c    g   ddd      
   d    c   ddd      
   e    g   ddd      
   f    g   ddd      
   g    x   ddd     id 为 节点标识，pid 为该节点父节点,现在需要查询该表, 得到如下结果   id  pid  childrenCount  name
   a    c      0            ddd  
   b    c      0            ddd   
   c    g      3            ddd   
   d    c      0            ddd    
   e    g      0            ddd     
   f    g      0            ddd  
   g    x      2            ddd 查询每条节点，并统计出每个节点的子代数量作为结果及的新列，名称为childrenCount

Answer 1

还是没搞清楚你的需求，c g ddd 这条是3 为什么 g x ddd 这条是2 呢？

Answer 2

 create table tb(id nvarchar(10),pid nvarchar(10),name nvarchar(10))
insert into tb select 'a','c','ddd'
insert into tb select 'b','c','ddd'
insert into tb select 'c','g','ddd'
insert into tb select 'd','c','ddd'
insert into tb select 'e','g','ddd'
insert into tb select 'f','g','ddd'
insert into tb select 'g','x','ddd'
go
select t1.id,t1.pid,isnull(t2.c,0)childrenCount,t1.name from tb t1 left join (
select a.id,count(b.id)as c from tb a inner join tb b on a.id=b.pid group by a.id
)t2 on t1.id=t2.id
go
drop table tb
/*
id         pid        childrenCount name
---------- ---------- ------------- ----------
a          c          0             ddd
b          c          0             ddd
c          g          3             ddd
d          c          0             ddd
e          g          0             ddd
f          g          0             ddd
g          x          3             ddd(7 行受影响)
*/

Answer 3

 select *,childrenCount=(select count(1) from tb where pid=a.id)
from tb a

Answer 4

create table tb
(
  id varchar(1),
  pid varchar(1), 
  name varchar(3)
)insert into tb
select 'a','c','ddd' union all
select 'b','c','ddd' union all
select 'c','g','ddd' union all
select 'd','c','ddd' union all
select 'e','g','ddd' union all
select 'f','g','ddd' union all
select 'g','x','ddd'select id
,pid
,(select COUNT(*) from tb b where a.id=b.pid) as childrenCount 
,name
from tb a
/*
id pid childrenCount name
a c 0 ddd
b c 0 ddd
c g 3 ddd
d c 0 ddd
e g 0 ddd
f g 0 ddd
g x 3 ddd
*/

Answer 5

或者:
 create table tb(id nvarchar(10),pid nvarchar(10),name nvarchar(10))
insert into tb select 'a','c','ddd'
insert into tb select 'b','c','ddd'
insert into tb select 'c','g','ddd'
insert into tb select 'd','c','ddd'
insert into tb select 'e','g','ddd'
insert into tb select 'f','g','ddd'
insert into tb select 'g','x','ddd'
go
select a.id,a.pid,count(b.id)as childrenCount,a.name from tb a left join tb b on a.id=b.pid group by a.id,a.pid,a.name
go
drop table tb
/*
id         pid        childrenCount name
---------- ---------- ------------- ----------
a          c          0             ddd
b          c          0             ddd
c          g          3             ddd
d          c          0             ddd
e          g          0             ddd
f          g          0             ddd
g          x          3             ddd(7 行受影响)
*/

Answer 6

 
create table tb(id nvarchar(10),pid nvarchar(10),name nvarchar(10))
insert into tb select 'a','c','ddd'
insert into tb select 'b','c','ddd'
insert into tb select 'c','g','ddd'
insert into tb select 'd','c','ddd'
insert into tb select 'e','g','ddd'
insert into tb select 'f','g','ddd'
insert into tb select 'g','x','ddd'select *,childrenCount=(select count(1) from tb where pid=a.id)
from tb a/*
id         pid         name      childrenCount
---------- ---------- ------------- ----------
a          c           ddd            0
b          c           ddd           0
c          g           ddd           3
d          c           ddd           0
e          g           ddd           0
f          g           ddd           0
g          x           ddd           3