求一SQL查询语句！

[cimzzxiang]

表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

[guguda2008]

SELECT AID
,(SELECT COUNT(1) FROM B WHERE B.A_ID=AID) AS [COUNT(A_ID)]
FROM A

[百年树人]

select a.id,count(b.a_id) as [count(a_id)]
from a
left join b on a.id=b.a_id
group by a.id

[cimzzxiang]

[Quote=引用 1 楼 guguda2008 的回复:]
SQL code
SELECT AID
,(SELECT COUNT(1) FROM B WHERE B.A_ID=AID) AS [COUNT(A_ID)]
FROM A
[/Quote]
可以了

[cimzzxiang]

求问:为什么写count(1)就可以了

[z812183667]

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) 是为了提高效率

[dawugui]

[Quote=引用 4 楼 cimzzxiang 的回复:]
求问:为什么写count(1)就可以了
[/Quote]你要是不能理解count(1),就用count(*)

create table A(id int,name varchar(10)) 
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)
go

select a.id , [count(a_id)] = (select count(*) from b where b.a_id = a.id) from a

drop table a , b

/*
id          count(a_id) 
----------- ----------- 
1           2
2           2
3           0

（所影响的行数为 3 行）
*/