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

# 思路：
# 查询完成高难度SQL得分平均值大于80分且7级的用户
# 统计该用户2021年试卷完成次数和题目总练习次数
# 由于有些用户完成试卷，但是没有练习；有些用户练习了但是没有完成试卷，因此用左右连接后union拼起来
select * from (
select t5.uid,ifnull(t5.exam_cnt,0) as exam_cnt
    ,ifnull(t5.question_cnt,0) as question_cnt 
from 
(select t1.*,t2.question_cnt from  (
select e3.uid,count(e3.submit_time) as exam_cnt from 
exam_record as e3 where year(e3.submit_time)= 2021 
    group by e3.uid) t1
left join (
select e4.uid,count(e4.submit_time) as question_cnt from 
practice_record as e4 where year(e4.submit_time)= 2021 
group by e4.uid )t2 on t1.uid=t2.uid ) t5
union 
 select t3.*,t4.question_cnt from (
select e3.uid,count(e3.submit_time) as exam_cnt from 
exam_record as e3 where year(e3.submit_time)= 2021 
    group by e3.uid ) t3 
right join (
select e4.uid,count(e4.submit_time) as question_cnt from 
practice_record as e4 where year(e4.submit_time)= 2021 
group by e4.uid )t4 on t3.uid=t4.uid ) t6 
where t6.uid in 
 (
select e1.uid from exam_record as e1
left join examination_info as e2 
on e1.exam_id=e2.exam_id 
left join user_info as u1 on e1.uid=u1.uid 
where e2.tag='SQL' and e2.difficulty = 'hard' 
and u1.level = 7 
group by e1.uid having avg(e1.score)  >= 80 ) order by t6.exam_cnt asc,t6.question_cnt desc