分析：

筛选对象——“高难度SQL试卷得分平均值大于80并且是7级的红名大佬”：

• 筛选方式：where uid in (select uid from…) （一定是in单列表）
• 筛选语句：

Where tag=‘SQL' and 'hard' and level=7 and score is not null
Group by uid
Having avg(score)>80

筛选变量——“统计他们的2021年试卷总完成次数和题目总练习次数，只保留2021年有试卷完成记录的用户”

• 筛选方式：（试卷记录表）left join （题目记录表）

由于对试卷完成次数有要求而对题目无要求，适合用left join 筛选；

但由于右边可能是null值，故定义question_cnt时应采取if(question_cnt is null,0,question_cnt)

• 时间限制的位置：left join两表都要放

由于两个次数都要求是在2021年间，则不如在表比较简单时就进行筛选；

不然会出现不同试卷记录对应相同题目记录，也就是同一记录出现多次的情况，由于对时间又有要求，这时用distinct也不好筛选

• 排序方式——结果按试卷完成数升序，按题目练习数降序

order by exam_cnt asc,question_cnt desc

提交答案：

易错点：标明变量所在表名，如q1.uid等

select q1.uid,exam_cnt,if(question_cnt is null,0,question_cnt)
from

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

left join

(select uid,count(question_id) as question_cnt
from practice_record
where year(submit_time)=2021
group by uid) as q2
on q1.uid=q2.uid

where q1.uid in 
  
(select er.uid
from exam_record as er
join user_info as ui 
on ui.uid=er.uid
join examination_info as ei
on er.exam_id=ei.exam_id
where tag='SQL' and difficulty='hard' and level=7 and submit_time is not null
group by uid
having avg(score)>80)

group by uid
order by exam_cnt asc,question_cnt desc