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

做法有些复杂。 总代码：

select qqq.uid,	act_month_total, act_days_2021, act_days_2021_exam, act_days_2021_question
from
(
  select ui.uid,
          count(distinct if(year(er.submit_time)=2021,date(er.submit_time),null)) as 	act_days_2021_exam,#date(er.submit_time)天数不含秒
          count(distinct if(year(pr.submit_time)=2021,date(pr.submit_time),null)) as act_days_2021_question
  from exam_record as er 
          left join practice_record as pr on er.uid = pr.uid
           right join user_info as ui on ui.uid = er.uid
  where `level` in (6,7)
  group by ui.uid
) as qqq 

left join 

(
select uid,
	count(distinct date_format(time,'%y%m')) as act_month_total,
	count(distinct if(year(time)=2021,date(time),null)) as act_days_2021
from(
    select ui.uid,start_time as time
    from exam_record as er right join user_info as ui on er.uid = ui.uid 
    union all 
    select uid,submit_time as time
    from practice_record
    ) as q
group by uid
) as qq 

on qqq.uid = qq.uid

order by act_month_total desc, act_days_2021 desc;

1、统计总的月份和2021年总的天数

统计总的月份和总的天数，最容易想到的就是把试卷时间和练习时间放在同一列，即纵向联结，然后去重做统计。

select uid,
    count(distinct date_format(time,'%y%m')) as act_month_total,
    count(distinct if(year(time)=2021,date(time),null)) as act_days_2021
from(
    select ui.uid,start_time as time
    from exam_record as er right join user_info as ui on er.uid = ui.uid
    union all
    select uid,submit_time as time
    from practice_record
    ) as q
group by uid

这里需要注意：在试卷表和练习表中，没有1005用户，但是输出我们需要将它输出，因为没有记录所以总的月份数和天数全为0。所以需要将它的记录加上。 这样就得到了表的前两列。

2、统计试卷和练习在2021年的天数

统计试卷和练习的天数，把试卷表和练习表横向合并，然后去重统计。

select ui.uid,
       count(distinct if(year(er.submit_time)=2021,date(er.submit_time),null)) as    act_days_2021_exam,#date(er.submit_time)天数不含秒
       count(distinct if(year(pr.submit_time)=2021,date(pr.submit_time),null)) as act_days_2021_question
from exam_record as er
       left join practice_record as pr on er.uid = pr.uid
       right join user_info as ui on ui.uid = er.uid
where `level` in (6,7)
group by ui.uid

3、将步骤1和步骤2的表合并

因为步骤1和步骤2都将用户信息表合并在一起了，所以包含了所有用户uid。所以联结方式不影响。