题解 | #每个6/7级用户活跃情况#可能是最简洁的mysql代码

--两个易错点：1.用 with cte as 通用表表达式的cte做最左边的表左连接其他表才能取全uid
2.当count计数需要多条件筛选并去重时，distinct写在if外面，if内筛选条件可以为多个用and连接，即count（distinct if（筛选条件，v1，null））
技巧：巧用count多条件筛选可以通过一次分组就聚合出题目所需的四个数据啦，减少了代码量，运行效率也不错

全部代码如下：
with cte as (select uid from user_info where level in (6,7))
select uid,count(distinct date_format(submit_time,'%Y%m')) as act_mon,
count(distinct if(year(submit_time)=2021,date(submit_time),null)) as act_2021day,
count(distinct if(year(submit_time)=2021 and idd in (select distinct exam_id from exam_record), date(submit_time),null)) as act_2021day_exam,
count(distinct if(year(submit_time)=2021 and idd in (select distinct question_id from practice_record),date(submit_time),null)) as act_2021day_question
from(
select c.uid,idd,submit_time,score
from cte c
left join
(
select uid,exam_id as idd,submit_time,score from exam_record
union
select uid,question_id as idd,submit_time,score from practice_record) b
on c.uid=b.uid ) d
group by uid
order by act_mon desc,act_2021day desc ;