题解 | #满足条件的用户的试卷完成数和题目练习数#

select uid,
COUNT(if(test='exam_id',1,NULL)) as exam_cnt ,#统计考试类别的试卷数
COUNT(if(test='question_id',1,NULL)) as question_cnt#统计练习类别的试卷数
from(
select uid,'exam_id' as test
from exam_record
where year(submit_time)='2021' and score is not null 
union all
select uid,'question_id' as test
from practice_record
where year(submit_time)='2021' and score is not null 
)k -- 筛选符合条件的试卷
where uid in -- 筛选大佬
(
select ui.uid
from user_info as ui
inner join exam_record as er on ui.uid = er.uid
inner join examination_info ei on ei.exam_id=er.exam_id
where ui.level=7 and ei.tag='SQL' and
      ei.difficulty='hard' and year(submit_time)='2021'
group by ui.uid
having avg(score)>80
) 
group by uid
order by exam_cnt asc,question_cnt desc