一个联表统计的问题

searchMe
  • 81

有两张表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语句该怎么写

回复
阅读 1k
4 个回答

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

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

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

你知道吗?

宣传栏