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

SELECT a.uid uid,exam_cnt,question_cnt
FROM

(SELECT tb.uid uid,COUNT(submit_time) exam_cnt
FROM 
(SELECT uid FROM 
(SELECT a.uid uid
FROM user_info a JOIN exam_record b
ON a.uid=b.uid
JOIN examination_info c
ON c.exam_id=b.exam_id
WHERE level=7
AND tag='SQL'
AND difficulty='hard'
GROUP BY uid
HAVING AVG(score)>80)ta
WHERE uid IN
(SELECT uid FROM 
exam_record
WHERE YEAR(submit_time)=2021))tb
LEFT JOIN 
(SELECT * FROM exam_record
WHERE YEAR(submit_time)=2021)b
ON tb.uid=b.uid
GROUP BY tb.uid)a
JOIN
(SELECT tb.uid uid,COUNT(submit_time) question_cnt
FROM 
(SELECT uid FROM 
(SELECT a.uid uid
FROM user_info a JOIN exam_record b
ON a.uid=b.uid
JOIN examination_info c
ON c.exam_id=b.exam_id
WHERE level=7
AND tag='SQL'
AND difficulty='hard'
GROUP BY uid
HAVING AVG(score)>80)ta
WHERE uid IN
(SELECT uid FROM 
exam_record
WHERE YEAR(submit_time)=2021))tb
LEFT JOIN 
(SELECT * FROM practice_record
WHERE YEAR(submit_time)=2021)b
ON tb.uid=b.uid
GROUP BY tb.uid)b
ON a.uid=b.uid

ORDER BY exam_cnt,question_cnt DESC