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

感觉这样更简单也更容易理解（？）

sql知识只需要用到三个表连接

• 先求出act_days_2021_exam与act_days_2021_question
• 把这两者求和，然后减去其中重合的部分（date(er.start_time)=date(p.submit_time) ），即为act_days_2021
• act_month_total的逻辑与act_days_2021相同，求exam与question的和并减去重合部分

select u.uid, 

count(distinct date_format(er.start_time,'%Y%m'))+count(distinct date_format(p.submit_time,'%Y%m'))-count(distinct if(date_format(er.start_time,'%Y%m')=date_format(p.submit_time,'%Y%m'),er.start_time,null)) as act_month_total,

count(distinct if(year(er.start_time)=2021,date(er.start_time),null))
+count(distinct if(year(p.submit_time)=2021,date(p.submit_time),null))
-count(distinct if(date(er.start_time)=date(p.submit_time) and year(er.start_time)=2021,er.start_time,null)) as act_days_2021,

count(distinct if(year(er.start_time)=2021,date(er.start_time),null)) as act_days_2021_exam,

count(distinct if(year(p.submit_time)=2021,date(p.submit_time),null))  as act_days_2021_question

from user_info as u 
left join exam_record as er
on u.uid=er.uid 
left join practice_record as p
on u.uid=p.uid
where level=6 or level=7
group by u.uid
order by act_month_total desc, act_days_2021 desc