table a
{ id int,
shuoming varchar(20)
}
table b
{
id int,
pk_id int 外键依赖a
}
查询出a表所有数据及其id在b表出现的总数
如
1,aaa,100
2,bbb,0
3,ccc,20
{ id int,
shuoming varchar(20)
}
table b
{
id int,
pk_id int 外键依赖a
}
查询出a表所有数据及其id在b表出现的总数
如
1,aaa,100
2,bbb,0
3,ccc,20
from a join b on a.id=b.id group by a.id,a.shuoming
(select count(1) from b where b.id=t.id) as [总数]
from a t
(select count(1) from b where b.pk_id=t.id) as [总数]
from a tselect a.id,a.shuoming,count(1) as [总数]
from a join b on a.id=b.pk_id group by a.id,a.shuoming
from a
left join b
on a.id=b.pk_id
group by a.id,a.shuoming或这样。只有用LEFT JOIN才能完全显示出A表的数目
select *
from a
outer apply(select count(1) as cnt from b where a.id=b.pk_id) b
select *
from a
outer apply(select count(1) as cnt from b where a.id=b.pk_id) b
(
id int,
shuoming varchar(20)
)
insert into a(id,shuoming)
(
select 1,'aaa' union
select 2,'bbb' union
select 3,'ccc' union
select 4,'ddd' union
select 5,'eee' union
select 6,'fff'
)
create table b
(
id int,
pk_id int --外键依赖a
)
insert into b(id,pk_id)
(
select 1,1 union
select 2,1 union
select 3,1 union
select 4,3 union
select 5,4 union
select 6,5 union
select 7,6 union
select 8,1 union
select 9,3 union
select 10,5
)
查询出a表所有数据及其id在b表出现的总数
如
1,aaa,100
2,bbb,0
3,ccc,20
select a.id,a.shuoming,(select count(b.pk_id) from b where b.pk_id=a.id) from a
go
select *,
rn=(select count(1) from b where a.id=b.pk_id)
from a
(
id int primary key,
shuoming varchar(20)
)
declare @i int
set @i=1
while @i<5
begin
insert into a(id,shuoming) values(@i,str(@i))
set @i=@i+1
endcreate table b
(
id int identity(1,1) primary key,
pk_id int,
foreign key (pk_id) references a(id)
)delete from b
insert into b values(2)
insert into b values(2)
insert into b values(2)
insert into b values(1)答案1
select a.id,a.shuoming,count(1) as [总数]
from a join b on a.id=b.id group by a.id,a.shuoming(0 行受影响)答案2 加上了distince
select distinct a.id,a.shuoming,
(select count(1) from b where b.pk_id=a.id) as [总数]
from a,b 1 1 1
2 2 3
3 3 0
4 4 0
答案3
select *,cnt=(select count(1) from b where a.id=b.pk_id) from a
1 1 1
2 2 3
3 3 0
4 4 0
答案4
select a.id,a.shuoming,count(1) as [总数]
from a join b on a.id=b.pk_id group by a.id,a.shuoming1 1 1
2 2 3答案 5
select a.id,a.shuoming,count(b.id) as cnt
from a
left join b
on a.id=b.pk_id
group by a.id,a.shuoming1 1 1
2 2 3
3 3 0
4 4 0答案6
select *
from a
outer apply(select count(1) as cnt from b where a.id=b.pk_id) b
1 1 1
2 2 3
3 3 0
4 4 0答案 7
select a.id,a.shuoming,(select count(b.pk_id) from b where b.pk_id=a.id) from a
1 1 1
2 2 3
3 3 0
4 4 0答案 8
select *,
rn=(select count(1) from b where a.id=b.pk_id)
from a
1 1 1
2 2 3
3 3 0
4 4 0