拆解见代码块
查找的四个结果分别放在一个子表（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
在尝试其他方法的时候遇到这种情况，有大佬帮忙解答一下吗？
京公网安备 11010502036488号