sql语句count怎么同时统计行数为0的字段????

Question

有两个表a和b，a中有a_id,a_name,b中有b_id,a_id，我想分别统计b表中a的数目,如果没有就显示0
select a.*,count(*) as a_number from a,b where b.a_id=a.a_id group by a_id,a_name 查出来后我是想得到三列：a_id,a_name,a_number 这样语句的结果是错误的 怎么改正呢？急！！！！！

Answer 1

select a.a_aid, a.a_name, count(b.b_bid) as a_number
from a left join b on a.a_id = b.a_id
group by a.a_aid, a.a_name追问

但是查出来之后，比如b中没有a_id=2的记录，可是显示出来的结果不对呀

追答

try this, should be fine now:

select a.a_aid, a.a_name, sum(case when b.b_bid is null then 1 else 0 end) as a_number
from a left join b on a.a_id = b.a_id
group by a.a_aid, a.a_name

本回答被提问者采纳

Answer 2

select a.*,t.a_number
from a,(select a_id,count(a_id) as a_number from b group by a_id) as t
where t.a_id=a.a_id

我没看清，原来a_id在a表出现没在b表出现的也要统计啊。
select a.a_aid,a_name，count(b.a_id) as a_number
from a left join b on a.a_id = b.a_id
group by a.a_aid,a_name追问

但是查出来之后，比如b中没有a_id=2的记录，可是显示出来的结果不对呀

Answer 3

a.* 你能查出来才出鬼了 你都分组了,哪能查a.*啊 就将这里改了就没问题了