表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
if object_id('tempdb.dbo.#A') is not null drop table #A
create table #A([id] int,[name] varchar(4))
insert #A
select 1,'张三' union all
select 2,'李四' union all
select 3,'王五'
--> 测试数据:#B
if object_id('tempdb.dbo.#B') is not null drop table #B
create table #B([id] int,[a_id] int,[year] int)
insert #B
select 1,1,2009 union all
select 2,1,2010 union all
select 3,2,2009 union all
select 4,2,2010select a.id,COUNT(b.a_id) from #A a left join #B b
on a.id=b.a_id
group by a.id
create table B(id int,a_id int,[year] int)
insert into A select 1,'张三'
insert into A select 2,'李四'
insert into A select 3,'王五'
insert into B select 1,1,2009
insert into B select 2,1,2010
insert into B select 3,2,2009
insert into B select 4,2,2010
go
select A.id,isnull((select count(*) from B where a_id=A.id),0)as cnt from A
go
drop table A,B
/*
id cnt
----------- -----------
1 2
2 2
3 0(3 行受影响)
*/
FROM A
LEFT JOIN B ON A.id=B.a_id
GROUP BY A.id
from #B b
where b.a_id=a.[id])as snum
from #A a这个可以实现,
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 行)
*/