题解 | #SELECT 中判断的引用#

• 本题的重点在于 活跃天数的计算（由题目练习和试卷作答两种组成），而且无论是总活跃月数和活跃天数都会用到 “2021-09-01”这种日期 因此第一步先进行 日期 + 标签的制表，然后进行嵌套使用
• 然后在SELECT 中使用判断语句 case when ..then ...end/if (条件，正确，错误)
• 易错点： 审题时疏忽，每个6/7级用户 当然在最后应是以 用户表为主--不然会缺失不活跃用户；

SELECT ui.uid, 
       COUNT(DISTINCT date_format(act_time,'%Y%m')) as act_month_total,
       COUNT(DISTINCT case when year(act_time)='2021' then act_time end) as act_day_2021,
       COUNT(DISTINCT case when year(act_time)='2021' and tag="exam" then act_time end) as act_day_2021_exam,
       COUNT(DISTINCT case when year(act_time)='2021' and tag="question" then act_time end) as act_day_2021_question
FROM
(
SELECT uid,DATE(start_time) as act_time,'exam' as tag
        FROM exam_record er
        UNION ALL
        SELECT uid,DATE(submit_time) as act_time,'question' as tag
        FROM practice_record pr
) a RIGHT JOIN user_info ui
on a.uid = ui.uid
WHERE ui.level in(6,7)
GROUP BY ui.uid
ORDER BY act_month_total DESC, act_day_2021 DESC