题解 | #每个6/7级用户活跃情况#

拆解见代码块

查找的四个结果分别放在一个子表（4个子表）与用户表进行连接

重点是试卷作答和题目练习两个表的进行合并查询

以及ifnull()函数的使用

#条件：6/7级用户
#查询结果：用户总活跃月份数、
#2021年活跃天数、2021年试卷作答活跃天数、2021年答题活跃天数
#排序：照总活跃月份数、2021年活跃天数降序排序

select u.uid,ifnull(act_month_total,0),ifnull(act_days_2021,0), ifnull(act_days_2021_exam ,0),ifnull(act_days_2021_question,0)
 from user_info u
 left join (select uid,count(distinct concat(year(act_time),month(act_time))) as act_month_total
from (select uid as uid ,start_time as act_time from exam_record
union all
select uid as uid,submit_time as act_time from practice_record) b
group by uid) b1
on u.uid=b1.uid
left join (select uid,count(distinct concat(month(act_time),day(act_time))) as act_days_2021
from (select uid as uid ,start_time as act_time from exam_record
where year(start_time)='2021'
union all
select uid as uid,submit_time as act_time from practice_record
where year(submit_time)='2021' ) b
group by uid) b2
on u.uid=b2.uid
left join (select uid, count(distinct concat(month(submit_time),day(submit_time) )) as act_days_2021_exam
 from exam_record
 where year(submit_time)='2021'
 group by uid) b3
 on u.uid=b3.uid
 left join ( select uid, count(distinct concat(month(submit_time),day(submit_time) )) as act_days_2021_question
 from practice_record
 where year(submit_time)='2021'
 group by uid) b4
 on u.uid=b4.uid
 where level =6 or level =7
order by act_month_total desc , act_days_2021 desc 

在尝试其他方法的时候遇到这种情况，有大佬帮忙解答一下吗？

这是外连接的样子 1002，1003，1005明明是空的为什么用count的时候会被计上数？结果显示为1？？