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

#高难度SQL试卷得分平均值大于80并且是7级红名大佬
#2021年试卷总完成次数exam_cnt和题目总练习次数question_cnt
#只保留2021年有试卷完成记录的用户
#试卷完成数升序，题目练习数降序
select er.uid, count(distinct er.exam_id) exam_cnt, count(distinct pr.id) question_cnt
from exam_record er 
left join practice_record pr on er.uid = pr.uid
and year(er.submit_time) = 2021
and year(pr.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 level = 7 and tag = 'SQL' and difficulty = 'hard'
    group by er.uid
    having avg(score) >= 80 
)
group by er.uid
order by exam_cnt asc, question_cnt desc;


错误代码：
select er.uid, count(distinct er.exam_id) exam_cnt, count(distinct pr.id) question_cnt
from exam_record er 
left join practice_record pr on er.uid = pr.uid
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 level = 7 and tag = 'SQL' and difficulty = 'hard'
    group by er.uid
    having avg(score) >= 80 
)
and year(er.submit_time) = 2021
and year(pr.submit_time) = 2021
group by er.uid
order by exam_cnt asc, question_cnt desc;

原因：
on和where的执行顺序不同。
from->where->group by->having->select->order by->limit
left join 是在from范围类所以 先on条件筛选表，然后两表再做left join。
而对于where来说在left join结果再次筛选。

on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。