SQL复杂查询拆解

题目

以NOWCODER SQL134为例，现有用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间）；
试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）；
试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）；
题目练习记录表practice_record（uid用户ID, question_id题目ID, submit_time提交时间, score得分）；
要求：找到高难度SQL试卷得分平均值大于80并且是7级的红名大佬，统计他们的2021年试卷总完成次数和题目总练习次数，只保留2021年有试卷完成记录的用户。结果按试卷完成数升序，按题目练习数降序。

求解

一眼看去，题目需要的筛选条件多。所以不妨对要求进一步细化，将查询分为一个个小的步骤进行。
1.先完成“统计他们的2021年试卷总完成次数和题目总练习次数，只保留2021年有试卷完成记录的用户。”
这个条件与哪些表有关？
它需要把表exam_record的试卷完成次数和表question_record的练习题目次数进行查询：

#表exam_record的试卷完成次数
SELECT
    uid,
    COUNT(submit_time) AS exam_cnt
FROM exam_record
WHERE YEAR(submit_time)=2021
GROUP BY uid
#表question_record的练习题目次数
SELECT
    uid,
    COUNT(submit_time) AS question_cnt
FROM practice_record
WHERE YEAR(submit_time)=2021
GROUP BY uid

2.然后是进一步的筛选条件：“高难度SQL试卷得分平均值大于80并且是7级的红名大佬”。
这个筛选条件与哪些表有关？
它与examination_info(高难度、SQL试卷)，exam_record(得分平均值),user_info(7级的红名大佬)三个表有关。这里仅仅查询了字段uid是为了后续将这些查询组合起来，加入其他的字段没有意义（用不上）。

SELECT 
    uid
FROM exam_record
JOIN examination_info
    USING(exam_id)
JOIN user_info
    USING(uid)
WHERE tag = 'SQL' AND difficulty = 'hard' AND level = 7
GROUP BY uid
HAVING AVG(score)>=80

3.现在的问题是，完成了每个小的步骤的筛选，怎么把它们组合起来？
步骤1和步骤2有哪些字段是两者之间有关联的？
本题中uid贯穿始终。

SELECT 
    uid,
    exam_cnt,
    IF(question_cnt is NULL, 0, question_cnt)-- （如果有）将NULL值替换成0
FROM
(
SELECT 
    uid,
    count(submit_time) as exam_cnt
FROM exam_record
WHERE YEAR(submit_time) = 2021
GROUP BY uid) t1
LEFT JOIN --通过连接将exam_cnt和question_cnt组合在一起
(SELECT
    uid,
    COUNT(submit_time) AS question_cnt
FROM practice_record
WHERE YEAR(submit_time) = 2021
GROUP BY uid) t2
    USING(uid)
WHERE uid IN
(
SELECT
    uid
FROM exam_record
JOIN examination_info 
    USING(exam_id)
JOIN user_info 
    USING(uid)
WHERE tag = 'SQL' AND difficulty = 'hard' AND level = 7
GROUP BY uid
HAVING avg(score) >= 80
)
ORDER BY exam_cnt, question_cnt DESC