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

SELECT user_info.uid,
COUNT(DISTINCT DATE_FORMAT(act_time,'%Y-%m')) act_month_total,
COUNT(DISTINCT IF(YEAR(act_time)=2021,
                  DATE_FORMAT(act_time,'%Y-%m-%d'),NULL)) act_days_2021,
COUNT(DISTINCT IF(YEAR(exam_record.start_time)=2021,
                  DATE_FORMAT(exam_record.start_time,'%Y-%m-%d'),NULL)),
COUNT(DISTINCT IF(YEAR(practice_record.submit_time)=2021,
                  DATE_FORMAT(practice_record.submit_time,'%Y-%m-%d'),NULL))
FROM user_info LEFT JOIN exam_record ON user_info.uid=exam_record.uid
LEFT JOIN practice_record ON user_info.uid=practice_record.uid
LEFT JOIN (SELECT uid, start_time AS act_time FROM exam_record
           UNION 
           SELECT uid, submit_time AS act_time FROM practice_record) t1
ON user_info.uid=t1.uid
WHERE level=6 OR level=7
GROUP BY user_info.uid
ORDER BY act_month_total DESC, act_days_2021 DESC;

这道题的关键点在于，exam_record和practice_record的活跃时间怎么把它放到一起来统计，这样就可完成前两个计数要求。后两个计数要求直接按要求count就好了。要把两个时间放到一起，可以用UNION来解决。