难点1:

act_month_total 要算所有年份 不止是2021年 所以不能和后面几列一起用where过滤

难点2:

act_month_total分别求试卷和练习的月份的时候如果只是用两个count月份的count相加会出现重复月份的情况，即使你分别用了两个distinct，一种方法就是再分别建立两个子查询把它们分别查出来再去重，我用的更简便的直接分别两个count distinct相加，然后在他们的和的末尾减去case when两边的月份有重复的算1：相当于两部运算得出这个值

COUNT(DISTINCT date_format(r.start_time, '%Y-%m')) +

COUNT(DISTINCT date_format(p.submit_time, '%Y-%m' )) -

COUNT(DISTINCT CASE WHEN date_format(r.start_time, '%Y-%m' ) = date_format(p.submit_time, '%Y-%m' ) then 1 end) as act_month_total

同理后面 act_days_2021 也一样的求法

难点3:

两边的活跃天数其实都可以为0，存在一边为0另一边不为0的情况（最后的用例里面只出现了试卷不为0练习为0的情况，但实际上也可以试卷为0练习不为0，所以要把两边的 is null or都加上），所以在where过滤里不能仅仅是说2021开头的日期，还要考虑不活跃的情况：

where (r.start_time is null or r.start_time like '2021%') and (p.submit_time is null or p.submit_time like '2021%')

难点4:

如果全程用JOIN的话连表的过程中会直接过滤掉不活跃的用户导致最后输出没有所有用户，如果全程用LEFT JOIN来保留所有用户的话会导致最后输出的表里不活跃的entry全是null的情况，所以所有的列在最外层select字段都要加一层ifnull（，0）来生成0的值

最终代码

SELECT
    uid,
    IFNULL(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,
    COUNT(DISTINCT date_format(r.start_time, '%Y-%m')) + COUNT(DISTINCT date_format(p.submit_time, '%Y-%m' )) - COUNT(DISTINCT CASE WHEN date_format(r.start_time, '%Y-%m' ) = date_format(p.submit_time, '%Y-%m' ) then 1 end) as act_month_total
FROM
    exam_record r
LEFT JOIN
    practice_record p USING(uid)
LEFT JOIN
    examination_info USING(exam_id)
LEFT JOIN
    user_info USING(uid)    
WHERE
    level = 6 or level = 7
GROUP BY
    uid) a
USING(uid)
LEFT JOIN
(
SELECT 
    uid,
    COUNT(DISTINCT date_format(r.start_time, '%Y-%m-%d')) + COUNT(DISTINCT date_format(p.submit_time, '%Y-%m-%d' )) - COUNT(DISTINCT CASE WHEN date_format(r.start_time, '%Y-%m-%d' ) = date_format(p.submit_time, '%Y-%m-%d' ) then 1 end) as act_days_2021,
    COUNT(DISTINCT date_format(r.start_time, '%Y-%m-%d' )) as act_days_2021_exam,
    COUNT(DISTINCT date_format(p.submit_time, '%Y-%m-%d' )) as act_days_2021_question
FROM
    exam_record r
LEFT JOIN
    practice_record p USING(uid)
LEFT JOIN
    examination_info USING(exam_id)
LEFT JOIN
    user_info USING(uid)    
WHERE
    (r.start_time is null or r.start_time like '2021%') and (p.submit_time is null or p.submit_time like '2021%') and (level = 6 or level = 7)
GROUP BY
    uid) b
USING(uid)
WHERE
    level = 6 or level = 7
ORDER BY
    act_month_total DESC, act_days_2021 DESC

：