select a.aid,sum(case when b.aid is null then 0 else 1 end) as num from a left join b on a.aid=b.aid group by a.aid
select a.aid,count(*) as num from a left join b on a.aid=b.aid group by a.aid 东升哥这样写也可以吧
select a.*,cnt=(select count(1) from b where a.aid=b.aid) from b
select a.*,isnull(b.cnt,0) as cnt from a left join(select aid,count(1) as cnt from b group by aid) on a.aid=b.aid
select a.*,[num] from a OUTER APPLY (select count(*) as [num] from b where a.aid=aid) l
declare @a table(aid int,name varchar(10)) insert @a select 1 ,'a' union all select 2 ,'b' union all select 3 ,'c'declare @b table(aid int,name varchar(10)) insert @b select 1 ,'a' union all select 1 ,'b' union all select 3 ,'c' select *,cnt=(select count(1) from @b where a.aid=aid) from @a a /*(3 行受影响) aid name cnt ----------- ---------- ----------- 1 a 2 2 b 0 3 c 1(3 行受影响)*/
declare @a table(aid int,name varchar(10)) insert @a select 1 ,'a' union all select 2 ,'b' union all select 3 ,'c'declare @b table(aid int,name varchar(10)) insert @b select 1 ,'a' union all select 1 ,'b' union all select 3 ,'c' select * from @a a cross apply (select count(*) cnt from @b where a.aid=aid) b/* aid name cnt ----------- ---------- ----------- 1 a 2 2 b 0 3 c 1(3 行受影响) */这里用cross apply也是可以的
from a left join b on a.aid=b.aid
group by a.aid
select a.aid,count(*) as num
from a left join b on a.aid=b.aid
group by a.aid 东升哥这样写也可以吧
from a
left join(select aid,count(1) as cnt from b group by aid)
on a.aid=b.aid
from a OUTER APPLY (select count(*) as [num] from b where a.aid=aid) l
insert @a select 1 ,'a'
union all select 2 ,'b'
union all select 3 ,'c'declare @b table(aid int,name varchar(10))
insert @b select 1 ,'a'
union all select 1 ,'b'
union all select 3 ,'c'
select *,cnt=(select count(1) from @b where a.aid=aid) from @a a
/*(3 行受影响)
aid name cnt
----------- ---------- -----------
1 a 2
2 b 0
3 c 1(3 行受影响)*/
insert @a select 1 ,'a'
union all select 2 ,'b'
union all select 3 ,'c'declare @b table(aid int,name varchar(10))
insert @b select 1 ,'a'
union all select 1 ,'b'
union all select 3 ,'c'
select * from @a a cross apply (select count(*) cnt from @b where a.aid=aid) b/*
aid name cnt
----------- ---------- -----------
1 a 2
2 b 0
3 c 1(3 行受影响)
*/这里用cross apply也是可以的