思路

老办法啊，遇到这种套娃题目我们就拆：

**每个6/7级用户总活跃月份数、2021年活跃天数、2021年试卷作答活跃天数、2021年答题活跃天数，**按照总活跃月份数、2021年活跃天数降序排序

一眼看去，最简单的是什么？

2021年试卷作答活跃天数、2021年答题活跃天数

注意这里有一个坑：2021年活跃天数 != 2021年试卷作答活跃天数+2021年答题活跃天数，如果同一天完成试卷跟答题依然是一天

2021年试卷作答活跃天数

select
    uid,
    count(distinct date(submit_time))
FROM
    practice_record 
WHERE
    year(submit_time) = 2021
group by 
    uid

如法炮制

2021年答题活跃天数

select
  uid,
  count(distinct date(start_time))
FROM
  exam_record
WHERE
  year(start_time) = 2021
GROUP BY
  uid;

但是uid有问题，这俩uid不一定一样的，也就是说这俩表连的话不能确定谁是主表谁是从表

那咋办呢？这俩的uid一定在user_info里面于是就三表连接

用这个来进行筛选和数人。

count(distinct if(YEAR(e_r.start_time)=2021,date(e_r.start_time),null))
count(distinct if(YEAR(p_r.submit_time)=2021,date(p_r.submit_time),null))

综合

select
    u_i.uid,
    count(distinct if(YEAR(e_r.start_time)=2021,date(e_r.start_time),null)) act_days_2021_exam,
    count(distinct if(YEAR(p_r.submit_time)=2021,date(p_r.submit_time),null)) act_days_2021_queston
from
    exam_record e_r
right JOIN
    user_info u_i
ON
    e_r.uid = u_i.uid
LEFT JOIN
    practice_record p_r
ON
    u_i.uid = p_r.uid
WHERE
    level > 5
GROUP BY
    u_i.uid
order by
    act_month_total desc ,act_days_2021 desc;

顺带把level解决，那前面的俩难搞的怎么办呢？

总活跃月份数、2021年活跃天数

select
    uid,
    count(distinct  date_format(start_time, '%Y%m')) act_month_total,
    count(distinct if(YEAR(start_time)=2021,date_format(start_time, '%Y%m%d'),null)) act_days_2021
from (select
          uid,
          submit_time start_time
      from practice_record
      union
      select
          uid,
          start_time
      from exam_record) mon
group by
    uid

又是两层查询，一层链接表，一层筛选

所以将结果再连起来就成了

通过代码1

select
    u_i.uid,
    ifnull(act_month_total,0) act_month_total,
    ifnull(act_days_2021,0)act_days_2021,
    count(distinct if(YEAR(e_r.start_time)=2021,date(e_r.start_time),null)) act_days_2021_exam,
    count(distinct if(YEAR(p_r.submit_time)=2021,date(p_r.submit_time),null)) act_days_2021_queston
from
    exam_record e_r
right JOIN
    user_info u_i
ON
    e_r.uid = u_i.uid
LEFT JOIN
    practice_record p_r
ON
    u_i.uid = p_r.uid
left join (
        select
            uid,
            count(distinct  date_format(start_time, '%Y%m')) act_month_total,
            count(distinct if(YEAR(start_time)=2021,date_format(start_time, '%Y%m%d'),null)) act_days_2021
        from (select
                  uid,
                  submit_time start_time
              from practice_record
              union
              select
                  uid,
                  start_time
              from exam_record) mon
        group by
            uid
        ) mon
on mon.uid = u_i.uid
WHERE
    level > 5
GROUP BY
    u_i.uid
order by
    act_month_total desc ,act_days_2021 desc

代码又臭又长，表的利用率贼低，基本就是连接了6个表嵌套查询

问题出在哪？

select
    uid,
    submit_time start_time
from practice_record
union
select
    uid,
    start_time
from exam_record

这个表，理论上来说可以完成后两个要求，也就是我们的第一第二的需求，但是我们需要一个标记将上下块标记开，也就是标记出来做卷子跟做题

所以：

select
  uid,
  submit_time start_time,
  '1' tag
from practice_record
union
select
  uid,
  start_time,
  '2' tag
from exam_record

然后

count(distinct if(YEAR(start_time)=2021 and tag = '1',date(start_time),null)) act_days_2021_exam,
count(distinct if(YEAR(start_time)=2021 and tag = '2',date(start_time),null)) act_days_2021_queston

通过代码2

select
    u_i.uid,
    count(distinct  date_format(start_time, '%Y%m')) act_month_total,
    count(distinct if(YEAR(start_time)=2021,date_format(start_time, '%Y%m%d'),null)) act_days_2021,
    count(distinct if(YEAR(start_time)=2021 and tag = '2',date(start_time),null)) act_days_2021_exam,
    count(distinct if(YEAR(start_time)=2021 and tag = '1',date(start_time),null)) act_days_2021_queston
from (select
          uid,
          submit_time start_time,
          '1' tag
      from practice_record
      union
      select
          uid,
          start_time,
          '2' tag
      from exam_record) mon
right join user_info u_i
on u_i.uid = mon.uid
where u_i.level >5
group by
    uid
order by
    act_month_total desc ,act_days_2021 desc;

看起来顺眼多了