题解 | #未完成试卷数大于1的有效用户#

```select er.uid,
	count(distinct if(year(er.submit_time)=2021,er.exam_id,null),er.submit_time) as exam_cnt,
	count(distinct if(year(pr.submit_time)=2021,pr.question_id,null),pr.submit_time) as question_cnt
from exam_record as er left join  practice_record as pr on er.uid = pr.uid
where er.uid in (
select er.uid
from
	exam_record as er 
	left join examination_info as ei on er.exam_id = ei.exam_id
	right join user_info as ui on er.uid = ui.uid
where difficulty = 'hard' and `level` = 7 and tag = 'SQL'
group by er.uid
having avg(score)>80
)
group by er.uid
order by exam_cnt, question_cnt desc;

注意： 不能直接count(distinct er.exam_id,er.submit_time) as exam_cnt和count(distinct pr.question_id,pr.submit_time) as question_cnt。 可以看到1006有一行在2021年刷了试卷，但是在2020年刷了题，对exam_cnt需要计算，但是对question_cnt不能计算。所以使用count(if)。 另外不能再where后面加year(er.submit_time)=2021 and year(pr.submit_time)=2021。这样会直接把这行过滤掉。 因为对答案来说，同一个用户id刷同一套试卷再不同时间刷的需要多次计算。