表A,表B
A(id,name) B(id,a_id,year)
例:A id name
1 张三
2 李四
3 王五
B id a_id year
1 1 2009
2 1 2010
3 2 2009
4 2 2010
要求,查询B表中出现A的次数(如3没出现,但仍显示次数为0):
a_id count(a_id)
1 2
2 2
3 0
A(id,name) B(id,a_id,year)
例:A id name
1 张三
2 李四
3 王五
B id a_id year
1 1 2009
2 1 2010
3 2 2009
4 2 2010
要求,查询B表中出现A的次数(如3没出现,但仍显示次数为0):
a_id count(a_id)
1 2
2 2
3 0
,(SELECT COUNT(1) FROM B WHERE B.A_ID=AID) AS [COUNT(A_ID)]
FROM A
from a
left join b on a.id=b.a_id
group by a.id
create table #A(id int ,[name] varchar(20))
create table #B(id int ,a_id int,[year] varchar(20))
go
insert into #A values(1,'张三')
insert into #A values(2,'李四')
insert into #A values(3,'王五')insert into #B values(1,1,'2009')
insert into #B values(2,1,'2010')
insert into #B values(3,2,'2009')
insert into #B values(4,2,'2010')
Select #A.id as a_id,(Select count(*) From #B where #B.a_id=#A.id) as 数量 From #A
/*结果
a_id 数量
1 2
2 2
3 0
*/count(1) 是为了提高效率
create table B(id int,a_id int,[year] int)
insert into a values(1 ,'张三')
insert into a values(2 ,'李四')
insert into a values(3 ,'王五')
insert into b values(1 ,1 ,2009)
insert into b values(2 ,1 ,2010)
insert into b values(3 ,2 ,2009)
insert into b values(4 ,2 ,2010)
goselect a.id , [count(a_id)] = (select count(*) from b where b.a_id = a.id) from adrop table a , b/*
id count(a_id)
----------- -----------
1 2
2 2
3 0(所影响的行数为 3 行)
*/