sql查询一个表的id在另一个表里面出现的次数(结果出现在一个表里)
- 先建立一个子查询,先将user_login_hall表中的user_id分组统计出来
select user_id, count(1) from user_login_hall group by user_id
- 然后再将user表和子查询做个左外连接,并去除需要的字段,在后面加一些查询where条件
select *,uc.no as num from user_account as ua left join (select
user_id, count(1) as no_a from user_login_hall group by user_id) as uc
on ua.id=uc.user_id where to_days(createdate) = to_days(‘2019-07-31’)
- 可以加上IFNULL防止出现null,把次数为null的变成0
select *,IFNULL(uc.no_a,0) as num from user_account as ua left join
(select user_id, count(1) as no_a from user_login_hall group by
user_id) as uc on ua.id=uc.user_id where to_days(createdate) =
to_days(‘2019-07-31’)