一个联表统计的问题

Question

有两张表a表和b表，a表的数据是唯一的为主表，a.aname和b.bname是相同的，可以用来关联两个表，b表中是多条数据，意思就是一对多的关系，现在我想统计b表的条数 想要的格式是:array(n)( 0=>array(2)( ['aname']=> xxx, ['count']=> m, ) ) 统计的m为b表中的条数 并且b.bname=a.aname 请问 sql语句该怎么写

Answer 1

SELECT COUNT(b.bname) as count,aname FROM a LEFT JOIN b on a.aname = b.bname
GROUP BY aname

Answer 2

group by a.aname

Answer 3

SELECT COUNT(*) as `count`,aname FROM a RIGHT JOIN b on a.aname = b.bname
GROUP BY aname 

Answer 4

select b.bname,count(b.bname) as count from b left join a on a.aname = b.bname group by b.bname;
全部展示b表的情况
select b.bname,count(b.bname) as count from b right join a on a.aname = b.bname group by b.bname;
全部展示a表的情况
select b.bname,count(b.bname) as count from b inner join a on a.aname = b.bname group by b.bname;
只输出有双方都有的情况