查一个表出2组数据

Question

表a
id   |    father_id    |    name
-----------------------------------
1            0               xx
2            1               zz
3            1               cc
4            2               vv
5            1               bb
6            2               nn我希望用一句sql查询出这个表，然后再多加一个列count(*) as expl
expl列的含义是该行id值下  该表中father_id=该行id的记录的个数
得到的记录集应该是如下结果id   |    father_id    |    name    |    expl
---------------------------------------------
1            0               xx           3
2            1               zz           2
3            1               cc           0
4            2               vv           0
5            1               bb           0
6            2               nn           0

Answer 1

create table tb(id int, father_id int, name varchar(10))
insert into tb values(1 , 0 , 'xx') 
insert into tb values(2 , 1 , 'zz') 
insert into tb values(3 , 1 , 'cc') 
insert into tb values(4 , 2 , 'vv') 
insert into tb values(5 , 1 , 'bb') 
insert into tb values(6 , 2 , 'nn')
go
select * , expl = (select count(1) from tb where father_id = t.id) from tb tdrop table tb /*
id          father_id   name       expl        
----------- ----------- ---------- ----------- 
1           0           xx         3
2           1           zz         2
3           1           cc         0
4           2           vv         0
5           1           bb         0
6           2           nn         0（所影响的行数为 6 行）
*/

Answer 2

select 
  a.*,
  isnull(b.expl,0) as expl
from a
left join (select father_id as id,count(1) as expl from a group by father_id)
on a.id=b.id
  

Answer 3

if object_id('a') is not null
drop table a
go
create table a(id int,father_id int,name nvarchar(20)) 
goinsert into a select 
1,0,  'xx' union all select 
2,1,  'zz' union all select  
3,1,  'cc' union all select  
4,2,  'vv' union all select  
5,1,  'bb' union all select  
6,2,  'nn' select id,father_id,name,(select count(1) from a where father_id=a1.id) as exp1
from a a1/*id          father_id   name                 exp1        
----------- ----------- -------------------- ----------- 
1           0           xx                   3
2           1           zz                   2
3           1           cc                   0
4           2           vv                   0
5           1           bb                   0
6           2           nn                   0*/

Answer 4

select 
  a.*,
  isnull(b.expl,0) as expl
from a
left join (select father_id as id,count(1) as expl from a group by father_id) b
on a.id=b.id/**
id          father_id   name       expl
----------- ----------- ---------- -----------
1           0           xx         3
2           1           zz         2
3           1           cc         0
4           2           vv         0
5           1           bb         0
6           2           nn         0(6 行受影响)
**/