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

SELECT 
    t1.uid,
    t1.exam_cnt AS exam_cnt,
    IFNULL(t2.question_cnt, 0) AS question_cnt
FROM (
    SELECT 
        uid,
        COUNT(submit_time) AS exam_cnt
    FROM exam_record 
    WHERE YEAR(submit_time) = 2021
    GROUP BY uid
) t1
LEFT JOIN(
    SELECT
        uid,
        COUNT(submit_time) AS question_cnt
    FROM practice_record 
    WHERE YEAR(submit_time) = 2021
    GROUP BY uid
) t2
    ON t1.uid = t2.uid
WHERE t1.uid IN(
    SELECT ui.uid
    FROM user_info ui
    JOIN exam_record er ON ui.uid = er.uid
    JOIN examination_info ei ON er.exam_id = ei.exam_id
    WHERE tag = 'SQL' and difficulty = 'hard' and level = 7
    GROUP BY ui.uid
    HAVING AVG(score) > 80 
)
ORDER BY exam_cnt, question_cnt DESC

实在是非常麻烦，很多细节......，尽量把筛选的条件凑到一块，使用WHERE IN子句吧。