一个联表统计的问题

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.bnameGROUP 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;只输出有双方都有的情况