题解 | #满足条件的用户的试卷完成数和题目练习数#
满足条件的用户的试卷完成数和题目练习数
https://www.nowcoder.com/practice/5c03f761b36046649ee71f05e1ceecbf
思路：把符合条件的用户挑出来，按用户分组，对于每个用户，用左连接把exam_record与examination_info连接起来，再对试卷的时间与练习的时间进行筛选
我犯过的错误：1.只对试卷的时间进行了筛选而没有对练习的时间进行筛选 2.在对练习的时间进行筛选时直接在where中加了year(practice_record.submit_time) = 2021，导致跑出来的结果中用户数量比答案少，这才想到左连接中有些practice_record.submit_time是null值 3. 在算exam_cnt时，没有对count里面的内容进行distinct,因为没有考虑到左连接可能会使一条record多次出现 4.在计算exam_cnt与question_cnt时，仅count distinct submit_time时不对的，应该count distinct exam_id,submit_time.
select
uid,
count(distinct exam_id,exam_record.submit_time) as exam_cnt,
count(distinct question_id,practice_record.submit_time) as question_cnt
from
exam_record
left join practice_record using(uid)
where
year(exam_record.submit_time) = 2021
and (year(practice_record.submit_time) = 2021 or practice_record.submit_time is null)
and uid in (
select
uid
from
exam_record
join examination_info using(exam_id)
join user_info using(uid)
where
tag = 'SQL'
and difficulty = 'hard'
and level = 7
group by
uid
having
avg(score) > 80
)
group by
uid
order by
exam_cnt,
question_cnt desc