求一条sql语句

[ajq1989]

有两张表关联
A :
id name flag
1 jack 1
2 marky 1
3 jodarn 2
B :
id flag ckid
1 1 1
2 2 2
3 3 3

结果要这样：
id , flag ,统计的结果
1 1 2
2 2 1

大概结果就是， 要显示B的数据，同时加一列显示，关联左表的数量

[yibey]

select  a.id,a.flag,count(*) as 统计的结果 from
（
select b.* ,a.id as aid from B b left join A a on b.flag = a.flag
) a group by a.id,a.flag

[cd731107]

select B.id,B.flag,统计的结果=count(1) 
from B ,A 
where B.flag=A.flag 
group by B.id,B.flag

[--小F--]

select
   b.id,b.flag,count(1) as 结果
from
   b join a
on
   b.flag=a.flag
group by
    b.id,b.flag

[laowang134]

create table #a (id int identity(1,1),aname varchar(20),flag int)   
insert into #a
select 'jack', 1 union
select 'marky', 1 union
select 'jodarn', 2 
create table #b (id int identity(1,1),flag varchar(20),chid int)   
insert into #b
select 1, 1 union
select 2, 2 union
select 3, 3 
-----------
select id,flag,jieguo=(select count(1) from #a where #a.flag=#b.flag) from #b
/*
id   flag  统计的结果
---  ----  ---------------
1	1	2
2	2	1
3	3	0
*/