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

SELECT a.uid,exam_cnt,COALESCE(question_cnt,0)
FROM
(SELECT t1.uid
FROM exam_record t1, user_info t2, examination_info t3
WHERE t1.uid=t2.uid AND t1.exam_id=t3.exam_id
    AND level=7
    AND difficulty='hard'
    AND tag='SQL'
    AND submit_time IS NOT NULL
    AND YEAR(submit_time)=2021
GROUP BY t1.uid
HAVING AVG(score)>80) a

LEFT JOIN

(SELECT uid,COUNT(score) AS exam_cnt
FROM exam_record
WHERE YEAR(submit_time)=2021
GROUP BY uid
) b
ON a.uid=b.uid

LEFT JOIN

(SELECT uid,COUNT(score) AS question_cnt
FROM practice_record
WHERE YEAR(submit_time)=2021
GROUP BY uid
) c
ON a.uid=c.uid

ORDER BY exam_cnt ASC,question_cnt DESC