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

# count没有符合条件的行返回None不返回0,左连接没有对应的值返回None,需要ifnull
select uid,ifnull(count(act_month_total),0) as act_month_total
,ifnull(act_days_2021,0) as act_days_2021
,ifnull(act_days_2021_exam,0) as act_days_2021_exam
,ifnull(act_days_2021_question,0) as act_days_2021_question
from user_info
left join 
(select uid,left(submit_time,7) as act_month_total from exam_record 
union
select uid,left(submit_time,7) as act_month_total from practice_record) as t1 using(uid)
left join
(select uid,count(act_days_2021) as act_days_2021 
from
(select uid,left(submit_time,10) as act_days_2021 from exam_record 
where year(submit_time)=2021  
union
select uid,left(submit_time,10) as act_days_2021 from practice_record 
where year(submit_time)=2021
) as tt 
group by uid
) as t2 using(uid)
left join 
(select uid,count(distinct left(submit_time,10)) as act_days_2021_exam from exam_record 
where year(submit_time)=2021
group by uid) as t3 using(uid)
left join 
(select uid,count(distinct left(submit_time,10))as act_days_2021_question from practice_record 
where year(submit_time)=2021
group by uid) as t4 using(uid)
where level in (6,7)
group by uid
order by act_month_total desc,act_days_2021	desc