select A.groupid,A.groupname,count(B.userid) from `group` A left join `user` B on A.groupid=B.parent group by A.groupid,A.groupname
思路大体为:查询USER表,根据USER表中的parent 到Group表中得到组名称,再在USER中使用聚合函数count(userID),在使用group by 对 组ID(parent)分组即可 ; 详细代码如下: select u.parent as groupid ,(select g.groupname from Group g where g.groupid=u.parent limit 1 ) as groupname ,count(u.userid) as total from User u where 1 group by u.parent
select A.groupid,A.groupname,count(B.userid) from group A left join user B on A.groupid=B.parent group by A.groupid,A.groupname
from `group` A left join `user` B on A.groupid=B.parent
group by A.groupid,A.groupname
详细代码如下:
select u.parent as groupid ,(select g.groupname from Group g where g.groupid=u.parent limit 1 ) as groupname ,count(u.userid) as total from User u where 1 group by u.parent
from group A left join user B on A.groupid=B.parent
group by A.groupid,A.groupname