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

SELECT ui.uid,COUNT(DISTINCT er.id) exam_cnt,COUNT(DISTINCT pr.id) question_cnt
FROM user_info ui INNER JOIN exam_record er ON ui.uid=er.uid
                    LEFT JOIN practice_record pr ON ui.uid=pr.uid
WHERE YEAR(er.submit_time)=2021 AND (YEAR(pr.submit_time)=2021 OR pr.submit_time IS NULL)
AND ui.uid in (SELECT ui.uid
                FROM user_info ui INNER JOIN exam_record er 
                ON ui.uid=er.uid
                INNER JOIN examination_info ei
                ON ei.exam_id=er.exam_id
                WHERE difficulty='hard'
                AND tag='SQL'
                AND level=7
                GROUP BY ui.uid
                HAVING AVG(er.score)>80)
GROUP BY ui.uid
ORDER BY exam_cnt,question_cnt DESC