A和W的区别 | #满足条件的用户的试卷完成数和题目练习数#

select a.uid uid, count(distinct e.score) exam_cnt, count(distinct p.score) question_cnt
from
(select u.uid uid, avg(score) avg
from user_info u
left join exam_record r
on u.uid = r.uid
left join examination_info i
on r.exam_id = i.exam_id
where tag = 'SQL' and difficulty = 'hard' and level = 7
group by uid) a
left join exam_record e
on a.uid = e.uid and year(e.submit_time) = 2021
left join practice_record p
on a.uid = p.uid and year(p.submit_time) = 2021
where avg > 80
group by uid order by exam_cnt, question_cnt desc;

and和where的区别，and不对结果进行筛选，会返回null值，where对结果进行筛选。