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

WITH a1 AS (
           SELECT uid, COUNT(*) AS exam_cnt
           FROM user_info a JOIN exam_record c USING (uid)
                            JOIN examination_info b USING (exam_id)
           WHERE level >= 7
             AND tag = 'SQL'
             AND difficulty = 'hard'
             AND YEAR(c.start_time) = '2021'
           GROUP BY uid
           HAVING AVG(score) >= 80
           ), #找到高难度SQL试卷得分平均值大于80并且是7级的红名大佬
     a2 AS (
     SELECT uid, IFNULL(COUNT(question_id),0) AS question_cnt
     FROM practice_record
     WHERE uid IN (
                  SELECT uid
                  FROM a1
                  )
       AND YEAR(submit_time) = '2021'
     GROUP BY uid
     ),       #统计他们的2021题目总练习次数
     a3 AS (
     SELECT uid, IFNULL(COUNT(*),0) AS exam_cnt
     FROM exam_record
     WHERE uid IN (
                  SELECT uid
                  FROM a1
                  )
       AND YEAR(submit_time) = '2021'
     GROUP BY uid
     )        #统计他们的2021年试卷总完成次数
SELECT a3.uid, a3.exam_cnt, IFNULL(a2.question_cnt,0) AS question_cnt
FROM a2 LEFT JOIN a3 ON a2.uid = a3.uid
WHERE a3.uid IS NULL
UNION ALL
SELECT a3.uid, a3.exam_cnt, IFNULL(a2.question_cnt,0) AS question_cnt
FROM a2 RIGHT JOIN a3 ON a2.uid = a3.uid
ORDER BY 2, 3 DESC;