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

这道题我的想法是先把主键uid的条件筛选放一边，分别用四个子查询获得四个列，然后基于主键齐全表（就是user_info）连接四个列，最后再筛选uid

这四个列中，前两个逻辑相似，以下

select uid,count(distinct st) act_month_total from
(select uid,substr(submit_time,1,7) st
from exam_record
union
select uid,substr(submit_time,1,7) st
from practice_record) t1
group by uid

select uid,count(distinct st) act_days_2021 from
(select uid,substr(submit_time,1,10) st,year(submit_time) yy
from exam_record
union
select uid,substr(submit_time,1,10) st,year(submit_time) yy
from practice_record) t2
where t2.yy=2021
group by uid

后两个也是逻辑相似且更简单

select uid,count(distinct st) act_days_2021_exam from
(select uid,substr(submit_time,1,10) st,year(submit_time) yy
from exam_record
) t3
where t3.yy=2021
group by uid

select uid,count(distinct st) act_days_2021_question from
(select uid,substr(submit_time,1,10) st,year(submit_time) yy
from practice_record
) t4
where t4.yy=2021
group by uid

接下来用user_info表左连接它们（为了保证所有uid都查到）

select 
    ui.uid,
    ifnull(act_month_total,0) act_month_total,
    ifnull(act_days_2021,0) act_days_2021,
    ifnull(act_days_2021_exam,0) act_days_2021_exam,
    ifnull(act_days_2021_question,0) act_days_2021_question
from user_info ui


left join
(select uid,count(distinct st) act_month_total from
(select uid,substr(submit_time,1,7) st
from exam_record
union
select uid,substr(submit_time,1,7) st
from practice_record) t1
group by uid) T1 on ui.uid=T1.uid

left join
(select uid,count(distinct st) act_days_2021 from
(select uid,substr(submit_time,1,10) st,year(submit_time) yy
from exam_record
union
select uid,substr(submit_time,1,10) st,year(submit_time) yy
from practice_record) t2
where t2.yy=2021
group by uid) T2 on ui.uid=T2.uid

left join 
(select uid,count(distinct st) act_days_2021_exam from
(select uid,substr(submit_time,1,10) st,year(submit_time) yy
from exam_record
) t3
where t3.yy=2021
group by uid) T3 on ui.uid=T3.uid

left join
(select uid,count(distinct st) act_days_2021_question from
(select uid,substr(submit_time,1,10) st,year(submit_time) yy
from practice_record
) t4

最后再加上Uid的筛选即可

select 
    ui.uid,
    ifnull(act_month_total,0) act_month_total,
    ifnull(act_days_2021,0) act_days_2021,
    ifnull(act_days_2021_exam,0) act_days_2021_exam,
    ifnull(act_days_2021_question,0) act_days_2021_question
from user_info ui

left join
(select uid,count(distinct st) act_month_total from
(select uid,substr(submit_time,1,7) st
from exam_record
union
select uid,substr(submit_time,1,7) st
from practice_record) t1
group by uid) T1 on ui.uid=T1.uid

left join
(select uid,count(distinct st) act_days_2021 from
(select uid,substr(submit_time,1,10) st,year(submit_time) yy
from exam_record
union
select uid,substr(submit_time,1,10) st,year(submit_time) yy
from practice_record) t2
where t2.yy=2021
group by uid) T2 on ui.uid=T2.uid

left join 
(select uid,count(distinct st) act_days_2021_exam from
(select uid,substr(submit_time,1,10) st,year(submit_time) yy
from exam_record
) t3
where t3.yy=2021
group by uid) T3 on ui.uid=T3.uid

left join
(select uid,count(distinct st) act_days_2021_question from
(select uid,substr(submit_time,1,10) st,year(submit_time) yy
from practice_record
) t4

 
where t4.yy=2021
group by uid) T4 on ui.uid=T4.uid
where ui.uid in
(select uid from user_info where level=6 or level=7)
order by act_month_total desc, act_days_2021 desc

这样一来就把问题拆解完成了

或许不是最简单优雅的方法，也或许很多人和我想得差不多，但感觉还是思路很清晰哒