求一个简单的sql语句

[wsaxie]

buyuser表
id car_id
1 1
2 1
3 2
4 6

cars表
car_id car_name
1 宝马
2 奔驰
3 甲壳虫
4 广州丰田
5 大众
6 标致
7 福特

汇总表
car_id car_name count
1 宝马 2
2 奔驰 1
3 甲壳虫 0
4 广州丰田 0
5 大众 0
6 标致 1
7 福特 0

如何用sql语句实现buyuser表汇总后加入cars得到汇总表效果

[cailee]

select a.car_id,a.car_name,isnull(b.cnt,0) as count
from cars a left join 
(
select car_id,count(id) as cnt
from buyuser
)b
on a.car_id=b.car_id

[cailee]

修正：

select a.car_id,a.car_name,isnull(b.cnt,0) as count
from cars a left join 
(
select car_id,count(id) as cnt
from buyuser
group by car_id
)b
on a.car_id=b.car_id

[mbh0210]

   declare @buyuser TABLE( [id] int , car_id int)
   insert into @buyuser values( 1   ,   1 )
   insert into @buyuser values( 2   ,   1 )
   insert into @buyuser values( 3   ,   2 )
   insert into @buyuser values( 4   ,   6 )

   declare @cars table( car_id int, car_name varchar(50))
 insert into @cars values( 1   ,     '宝马') 
 insert into @cars values( 2   ,     '奔驰') 
 insert into @cars values( 3   ,     '甲壳虫') 
 insert into @cars values( 4   ,     '广州丰田') 
 insert into @cars values( 5   ,     '大众') 
 insert into @cars values( 6   ,     '标致') 
 insert into @cars values( 7   ,     '福特')  


  select a.* ,isnull(b.[count],0) as [count] from @cars a 
  left outer join( select car_id,count(*) as [count] from @buyuser group by car_id )b on b.car_id =a.car_id

[fwacky]

create table  buyuser ( [id] int , car_id int)
insert into buyuser values( 1   ,   1 )
insert into buyuser values( 2   ,   1 )
insert into buyuser values( 3   ,   2 )
insert into buyuser values( 4   ,   6 )

create table  cars ( car_id int, car_name varchar(50))
insert into cars values( 1   ,     '宝马') 
insert into cars values( 2   ,     '奔驰') 
insert into cars values( 3   ,     '甲壳虫') 
insert into cars values( 4   ,     '广州丰田') 
insert into cars values( 5   ,     '大众') 
insert into cars values( 6   ,     '标致') 
insert into cars values( 7   ,     '福特')  

select *, count =(select count(1)from  buyuser where car_id = A.car_id ) from cars A
==============
car_id      car_name                                           count
----------- -------------------------------------------------- -----------
1           宝马                                                 2
2           奔驰                                                 1
3           甲壳虫                                                0
4           广州丰田                                               0
5           大众                                                 0
6           标致                                                 1
7           福特                                                 0

(7 行受影响)