select a.aid ,a.name,count(a.aid) from a inner join b on a.aid = b.aid group by a.aid
--再改一下 select a.aid ,max(a.name),count(a.aid) from a inner join b on a.aid = b.aid group by a.aid
还有一点补充! 查询后的结果如果在B表中没有记录则为0; aid name count --------------------- 01 aaaa 2 02 bbbb 2 03 cccc 2 04 dddd 0
select a.aid ,max(a.name),sum(case when b.aid is null then 0 else 1 end) from a left join b on a.aid = b.aid group by a.aid
select a.aid ,max(a.name),sum(case when b.aid is null then 0 else 1 end) from a left join b on a.aid = b.aid group by a.aidAID MAX(A.NAME) SUM(CASEWHENB.AIDISNULLTHEN0EL -------------------- -------------------- ------------------------------ 01 aaaa 2 02 bbbb 2 03 cccc 2 4 dddd 0--上面是我的测试结果,不知道你测试了没有呢?
from a
inner join b
on a.aid = b.aid
group by a.aid
select a.aid ,max(a.name),count(a.aid)
from a
inner join b
on a.aid = b.aid
group by a.aid
查询后的结果如果在B表中没有记录则为0;
aid name count
---------------------
01 aaaa 2
02 bbbb 2
03 cccc 2
04 dddd 0
from a
left join b
on a.aid = b.aid
group by a.aid
from a
left join b
on a.aid = b.aid
group by a.aidAID MAX(A.NAME) SUM(CASEWHENB.AIDISNULLTHEN0EL
-------------------- -------------------- ------------------------------
01 aaaa 2
02 bbbb 2
03 cccc 2
4 dddd 0--上面是我的测试结果,不知道你测试了没有呢?