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

# 两次连表
SELECT
    uid,
    exam_cnt,
    IFNULL(question_cnt,0) question_cnt
FROM(
    # 统计不同用户的试卷完成数，2021年没有记录的直接筛掉
    SELECT
        uid,COUNT(DISTINCT exam_id) exam_cnt
    FROM
        exam_record
    WHERE
        YEAR(start_time) = 2021
    GROUP BY
        uid
) t1
# 左连接
LEFT JOIN(
    # 统计不同用户的题目总练习次数，注意不用DISTINCT去重
    SELECT
        uid,COUNT(question_id) question_cnt
    FROM
        practice_record
    WHERE
        YEAR(submit_time) = 2021
    GROUP BY
        uid
) t2 USING(uid)
# 内连接，起到筛选作用
INNER JOIN(
    # 找到符合条件的用户id
    SELECT
        uid, AVG(score) avg_score
    FROM 
        user_info 
    INNER JOIN
        exam_record USING(uid)
    INNER JOIN
        examination_info USING(exam_id)
    WHERE 
        tag='SQL' AND difficulty='hard' AND level=7
    GROUP BY
        uid
    HAVING
        avg_score > 80
) t3 USING(uid)
ORDER BY
    exam_cnt,question_cnt DESC