-- with临时表获得大佬id
with
t1 as
(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 tag='SQL' and difficulty ='hard' and level =7
group by uid
having avg(score) >80)

-- 两个子查询分别获得完成试卷数和题目数,sj试卷，tm题目
select uid,
(select count(1) from exam_record er where score is not null and er.uid=t1.uid and year(submit_time)=2021) as sj,
(select count(1) from practice_record pr where pr.uid=t1.uid and year(submit_time) =2021 ) as tm
from t1 order by sj,tm desc