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

SELECT exam_record.uid, COUNT(DISTINCT IF(YEAR(exam_record.submit_time)=2021,exam_record.id,NULL)) exam_cnt,
COUNT(DISTINCT IF(YEAR(practice_record.submit_time)=2021,practice_record.id,NULL)) question_cnt
FROM exam_record LEFT JOIN practice_record #要用左连接不能内连接
ON exam_record.uid=practice_record.uid
WHERE exam_record.uid IN (SELECT user_info.uid FROM user_info, exam_record
                          WHERE level=7 AND user_info.uid=exam_record.uid
                          AND exam_id IN (SELECT exam_id FROM examination_info WHERE tag='SQL' AND difficulty='hard')
                          GROUP BY user_info.uid
                          HAVING AVG(exam_record.score)>80)
GROUP BY exam_record.uid
ORDER BY exam_cnt,question_cnt DESC;

做得好曲折。

第一个错误点是把YEAR(exam_record.submit_time)=2021 AND YEAR(practice_record.submit_time)=2021 作为了WHERE后面的条件，这样会把2021年有exam记录的但是没有practice记录的过滤掉，不满足题目的要求。参考了别人的做法，在SELECT那里加条件做选择。其次注意要使用exam_record和practice_record左连接。

第二个错误点是“平均值大于80”，我错误的做法是先把exam_record和practice_record左连接后，再在WHERE后面加条件计算平均分：

WHERE exam_record.uid IN (SELECT uid FROM user_info WHERE level=7) AND 
exam_id IN (SELECT exam_id FROM examination_info WHERE tag='SQL' AND difficulty='hard')
GROUP BY exam_record.uid
HAVING AVG(exam_record.score)>80

这样错在把exam_record和practice_record左连接后，计算出来的平均成绩是错误的，因为exam_record和practice_record按照uid做了笛卡尔积，数据条数变多了，再按照uid分类算平均成绩算不对的。正确的做法是，用exam_record结合practice_record和user_info两个表中的“高难度SQL”和“7级的红名大佬”的条件，找出满足平均分大于80条件的uid，然后把它作为把刚刚左连接后表的约束，这样才能统计出正确的结果。

另有一个点的是要用COUNT(DISTINCT）来计算id，要用DISTINCT也是因为exam_record和practice_record做了笛卡尔积，数据条数变多了，没有DISTINCT就算重复了。