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

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

核心思路：

①: 构建Exam_Cnt和Practice_Cnt表,使用LEFT JOIN 在uid上连接

②: 选择满足条件的user_id, 并在WHERE子句中体现