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

SELECT
  uid,
  e_tb.exam_cnt exam_cnt,
  IF(p_tb.question_cnt IS NOT NULL, p_tb.question_cnt, 0) question_cnt
FROM
  (
    SELECT
      uid,
      COUNT(exam_id) exam_cnt
    FROM
      exam_record
    WHERE
      YEAR(submit_time) = 2021
    GROUP BY
      uid
  ) e_tb
  LEFT JOIN (
    SELECT
      uid,
      COUNT(question_id) question_cnt
    FROM
      practice_record
    WHERE
      YEAR(submit_time) = 2021
    GROUP BY
      uid
  ) p_tb USING(uid)
WHERE
  uid IN (
    SELECT
      uid
    FROM
      user_info ui
      INNER JOIN exam_record er USING(uid)
      INNER JOIN examination_info ei USING(exam_id)
    WHERE
      ei.difficulty = 'hard'
      AND ei.tag = 'SQL'
      AND ui.level = 7
    GROUP BY
      uid
    HAVING
      AVG(er.score) > 80
  )
ORDER BY
  exam_cnt,
  question_cnt DESC;