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

SELECT * 
FROM (
    SELECT a.uid, a.exam_cnt, IFNULL(b.question_cnt, 0) question_cnt
    FROM (
        SELECT uid, COUNT(*) exam_cnt
        FROM exam_record
        WHERE DATE_FORMAT(submit_time, '%Y') = 2021
        GROUP BY uid
    ) a 
    LEFT JOIN (
        SELECT uid, COUNT(*) question_cnt
        FROM practice_record
        WHERE DATE_FORMAT(submit_time, '%Y') = 2021
        GROUP BY uid
    ) b ON a.uid = b.uid
    UNION
    SELECT b.uid, IFNULL(a.exam_cnt, 0) exam_cnt, b.question_cnt
    FROM (
        SELECT uid, COUNT(*) exam_cnt
        FROM exam_record
        WHERE DATE_FORMAT(submit_time, '%Y') = 2021
        GROUP BY uid
    ) a 
    LEFT JOIN (
        SELECT uid, COUNT(*) question_cnt
        FROM practice_record
        WHERE DATE_FORMAT(submit_time, '%Y') = 2021
        GROUP BY uid
    ) b ON a.uid = b.uid
) a
WHERE a.uid IN (
    SELECT uid 
    FROM exam_record
    WHERE exam_id = (
        SELECT exam_id 
        FROM examination_info
        WHERE tag = 'SQL' AND difficulty = 'hard'
    ) 
    AND uid IN (
        SELECT uid 
        FROM user_info
        WHERE `level` = 7
    )
    GROUP BY uid 
    HAVING AVG(score) >= 80
)
ORDER BY exam_cnt ASC, question_cnt DESC;