select t3.uid,count(distinct case when year(t3.submit_time) = '2021' then t3.id else null end) as exam_cnt, 
count(distinct case when year(t4.submit_time) = '2021' then t4.id else null end) as question_cnt 
from exam_record t3
left join practice_record t4
on t3.uid = t4.uid

where t3.uid in(
select t1.uid
from exam_record t1
left join examination_info t2
on t1.exam_id = t2.exam_id
where t2.tag = "SQL"
and t1.uid in(
select uid 
from user_info
where level = 7)
group by t1.uid
having avg(t1.score) > 80)

group by t3.uid
order by exam_cnt asc , question_cnt desc ;

内层进行id的筛选，外层进行分组汇总查询