题解 | #满足条件的用户的试卷完成数和题目练习数#

-- 题目的坑是两次连续的left join，导致前一次左连接的整体再与下一个表左连接时，/
-- 若下一张表有多条记录能与第一张表的主键相关联，那么上两张表左关联的结果在与下一张表关联时 /
-- 记录会翻倍，最终导致第二张表的次数统计有误 /
-- 类似的，一二两张表的关联结果也会影响第三张表。
select 
    b1.uid,
    b1.exam_cnt,
    b2.question_cnt
from (
    select 
        a1.uid,
        count(a2.exam_id) exam_cnt
    from (    
        select 
            t3.uid
        from (
            select exam_id from examination_info where tag = 'SQL' and difficulty = 'hard'
        ) t1
        inner join (
            select uid,exam_id,score from exam_record
        ) t2
        on t1.exam_id = t2.exam_id
        inner join (
            select uid from user_info where level = 7
        ) t3
        on t2.uid = t3.uid
        group by t3.uid
        having avg(t2.score) >= 80
    ) a1
    left join (
        select uid,exam_id from exam_record where submit_time >= '2021-01-01 00:00:00' and submit_time <= '2021-12-31 23:59:59'
    ) a2 
    on a1.uid = a2.uid
    group by a1.uid
) b1
-- 需要分开单独进行两次左关联，并把次数统计出来；最后进行内连接将两个统计结果拼接
inner join (
    select 
        a1.uid,
        count(a2.question_id) question_cnt
    from (    
        select 
            t3.uid
        from (
            select exam_id from examination_info where tag = 'SQL' and difficulty = 'hard'
        ) t1
        inner join (
            select uid,exam_id,score from exam_record
        ) t2
        on t1.exam_id = t2.exam_id
        inner join (
            select uid from user_info where level = 7
        ) t3
        on t2.uid = t3.uid
        group by t3.uid
        having avg(t2.score) >= 80
    ) a1
    left join (
        select uid,question_id from practice_record where submit_time >= '2021-01-01 00:00:00' and submit_time <= '2021-12-31 23:59:59'
    ) a2
    on a1.uid = a2.uid
    group by a1.uid
) b2
on b1.uid = b2.uid 
order by b1.exam_cnt, b2.question_cnt desc ;