#先找高难度平均分大于90的用户id
/SELECT er.uid
FROM exam_record er JOIN examination_info ei
ON er.exam_id=ei.exam_id
WHERE ei.tag='SQL' AND ei.difficulty='hard'
GROUP BY er.uid
HAVING AVG(er.score) >=80;/
SELECT er.uid AS uid , COUNT( DISTINCT er.exam_id) AS exam_cnt, COUNT(DISTINCT pr.id) AS question_cnt
FROM exam_record er LEFT JOIN practice_record pr
ON er.uid=pr.uid AND YEAR(pr.submit_time)='2021' AND YEAR(er.submit_time)='2021'
WHERE er.uid IN (SELECT er.uid
FROM exam_record er LEFT JOIN examination_info ei
ON er.exam_id=ei.exam_id LEFT JOIN user_info ui
ON er.uid=ui.uid
WHERE ei.tag='SQL' AND ei.difficulty='hard' AND ui.level=7
GROUP BY er.uid
HAVING AVG(er.score) >=80)
GROUP BY er.uid
ORDER BY exam_cnt ASC, question_cnt DESC; #记录下别人遇到的两个问题:当然自己也遇到过。首先重要的是左查询,分清到底谁是主表,谁是从表。然后就是提期间过滤2021, #因为在where时候过滤,如果联系的是空值则无法进行记录 #1.怎么在后续筛选中,过滤掉非2021年的,发现在where后面加条件时,left join 建成的临时表的练习试卷对应的空值没法进行记录,看过大佬的答案后,发现可以“谓词下推”优化,在建立临时表的时候就将年份约束条件提前; #2.在count记录的时候,除了要以完成时间作为计数外,还要加上试卷id等;防止同一套试卷在两个时间段都完成了;这个我觉得上面足够了