SELECT a.uid,
COUNT(distinct case when year(r.submit_time)=2021 then r.id else null end)as exam_cnt,
COUNT(distinct case when year(p.submit_time)=2021 then p.id else null end)as question_cnt
from(select uid from exam_record 
     where uid in (select uid from user_info where  `level`=7)
     and exam_id in (select exam_id from examination_info where 
                     tag="SQL" and difficulty="hard")
     group by uid
     having sum(score)/count(score)>80)a
left join exam_record r
on a.uid=r.uid
left join practice_record p
on p.uid=a.uid
group by a.uid
order by exam_cnt asc, question_cnt desc;