满足条件的用户的试卷完成数和题目练习数
找到高难度SQL试卷得分平均值大于80并且是7级的红名大佬，统计他们的2021年试卷总完成次数和题目总练习次数，只保留2021年有试卷完成记录的用户。结果按试卷完成数升序，按题目练习数降序。

题解：
要求：

1.      ①高难度 ② SQL 的 ③试卷得分平均值 >80的 ④ 且 level=7的用户

2.      ⑤只保留2021年有试卷完成记录的用户

3.      统计这些用户 ⑥2021年 ⑦试卷总完成次数 和 ⑧ 题目总练习次数

4.      结果按⑨试卷完成数升序 ⑩按题目练习次数降序

难点（易犯错点）：

①     首先，被输出解释误导，题目中的 “试卷总完成次数”和‘题目总练习次数’解读会被误导为（计算试卷uid唯一数），其实是：假如1份试卷做了2次即‘试卷总完成次数’就为2，并不是说试卷份数是1。因此计算时可用count（submit）而不是count（distinct exam_id）
②     User_info表中，显示1006用户的level=6，在做检测时也为6，输出解释上也说是6，但实际上提交保存时，实际结果level=7；因为这个一直以为自己哪里出错了，但多次检查自己的筛选uid代码并没有错误，而陷入纠结中，导致浪费很多时间。

③     关于左连接：

  • 要考虑连接的共同关键字段哪一个的包围范围较多就放在前，不考虑可能会因把uid少的放在前左连接而丢失一些数据
  • 当先exam_record与practice_record左连接后计算时，而共同的uid若不唯一有多个，会导致有数据重复，用count（submit）计算“试卷总练习次数”和‘题目总练习次数’会错误；此时查看2个表的数据格式，其中id均是唯一键primary key，所以可以用count（distinct id）来计算
  • 关于连接2表后筛选各自2021年的数据，若在后面直接用and year（2个表2021时间）只会选出连接后submit_time共同为2021的数据，而exam_recor表为2021但practice_record表为null的数据被过滤掉了，那计算exam_cnt时数据不对。因此应要考虑筛选各自2021年而不影响另一表。
④     关于筛选exam_record表与practice_record表各自2021年的数据：
  • 方法一：分别统计2021年试卷练习次数和题目练习次数后，再以uid多的放前面左连接
  • 方法二：2表左连接后，在计算时用count(distinct if(year(submit_time)=2021,id,null))筛选2021年数据

方法一：分别计算后连接

步骤1：满足筛选条件要求的用户： 

select a.uid
from exam_record a 
left join examination_info b on a.exam_id=b.exam_id
inner join user_info c on a.uid=c.uid
where year(a.submit_time)=2021
and b.tag='SQL' and b.difficulty='hard' and c.level=7
group by a.uid
having avg(a.score)>80;

步骤2：统计exam_record表各用户2021年的‘试卷练习次数’： 

select uid,
    count(distinct exam_id) as exam_cnt
from exam_record 
where year(submit_time)=2021
group by uid;

步骤3：统计practice_record表各用户2021年的‘题目练习次数’： 

select uid,
    count(distinct question_id) as question_cnt
from practice_record 
where year(submit_time)=2021
group by uid;

最终代码： 

select uid,
    if(exam_cnt is null,0,exam_cnt),
    if(question_cnt is null,0,question_cnt)
from (select uid,
        count(submit_time) as exam_cnt
    from exam_record 
    where year(submit_time)=2021
    group by uid) t
left join (select uid,
        count(submit_time) as question_cnt
    from practice_record 
    where year(submit_time)=2021
    group by uid) t2
using(uid)
where uid in (select a.uid
    from exam_record a 
    left join examination_info b on a.exam_id=b.exam_id
    inner join user_info c on a.uid=c.uid
    where year(a.submit_time)=2021
    and b.tag='SQL' and b.difficulty='hard' and c.level=7
    group by a.uid
    having avg(a.score)>80)
order by exam_cnt asc,question_cnt desc;


方法二：exam_record表与practice_record表左连接后计算 

select uid,
    count(distinct if(year(a.submit_time)=2021,a.id,null)) as exam_cnt,
    count(distinct if(year(b.submit_time)=2021,b.id,null)) as question_cnt
from exam_record a
left join practice_record b using(uid)
where uid in (select uid
            from exam_record 
            inner join examination_info using(exam_id)
            inner join user_info using(uid)
            where tag='SQL'and difficulty='hard' and level=7
            and year(submit_time)=2021
            group by uid
            having avg(score)>80)
group by uid
order by exam_cnt asc,question_cnt desc;