统计某张表中的id在另一张表中出现的次数

今天同事给我看了一条SQL语句，是要统计会员member表中每个会员的签到次数，说到底就是要统计member表中的id在member_sign表中出现的次数。

其实只要构造一个子查询就好了。

先将member_sign表中的member_id分组统计出来

select count(*) from member_sign group by member_id

这样就得到每个签到表中会员格子的签到次数

然后再将member表和子查询做个左外连接,并去除需要的字段

select vm.id,vm.account,vs.num as num from vm_member as vm left join (select count(*) as num,member_id from vm_member_sign group by member_id) as vs on vm.id=vs.member_id

更完善点可以加上IFNULL防止出现null指

select vm.id,vm.account,IFNULL(vs.num,0) as num from vm_member as vm left join (select count(*) as num,member_id from vm_member_sign group by member_id) as vs on vm.id=vs.member_id