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

首先展示题目要求，然后根据条件进行拆解。

1. 题目要求
   找到高难度SQL试卷得分平均值大于80并且是7级的红名大佬，统计他们的2021年试卷完成数和题目练习数，只保留2021年有试卷完成记录的用户。结果按试卷完成数升序，按题目练习数降序。
2. 拆解

• 高难度SQL试卷-----where
• 得分平均值大于80----group by + having
• 7级的红名大佬-----where
• 2021年有试卷完成记录的用户-----where
• 统计他们的2021年试卷完成数和题目练习数-----count
• 按试卷完成数升序，按题目练习数降序-----order by

3. 结果

• 找到高难度SQL试卷得分平均值大于80并且是7级的红名大佬

select ui.uid
from examination_info as ei 
join exam_record as er 
on ei.exam_id = er.exam_id
join user_info as ui 
on ui.uid = er.uid
where ei.tag = "SQL"
and ui.level = 7
and ei.difficulty = "hard"
group by ui.uid
having avg(er.score) > 80

• count 计算 exam_id，获得 exam_cnt 结果，记为表 t1

select er.uid, count(distinct er.exam_id) as exam_cnt
from exam_record as er
where year(er.start_time) = 2021
and er.uid in (select ui.uid
from examination_info as ei 
join exam_record as er 
on ei.exam_id = er.exam_id
join user_info as ui 
on ui.uid = er.uid
where ei.tag = "SQL"
and ui.level = 7
and ei.difficulty = "hard"
group by er.uid
having avg(er.score) > 80)
group by er.uid

• count 计算 question_id，获得 question_cnt 结果，记为表 t2

select pr.uid, ifnull(count(question_id), 0) as question_cnt
from practice_record as pr
where year(pr.submit_time) = 2021
and pr.uid in (select ui.uid
from examination_info as ei 
join exam_record as er 
on ei.exam_id = er.exam_id
join user_info as ui 
on ui.uid = er.uid
where ei.tag = "SQL"
and ui.level = 7
and ei.difficulty = "hard"
group by er.uid
having avg(er.score) > 80)
group by pr.uid

• 将 t1 和 t2 通过 left join 连接，对于出现空值部分使用 ifnull 函数进行转化，即最终结果

select t1.uid, t1.exam_cnt, ifnull(t2.question_cnt, 0) as question_cnt
from (
select er.uid, count(distinct er.exam_id) as exam_cnt
from exam_record as er
where year(er.start_time) = 2021
and er.uid in (select ui.uid
from examination_info as ei 
join exam_record as er 
on ei.exam_id = er.exam_id
join user_info as ui 
on ui.uid = er.uid
where ei.tag = "SQL"
and ui.level = 7
and ei.difficulty = "hard"
group by er.uid
having avg(er.score) > 80)
group by er.uid
) as t1

left join (

select pr.uid, ifnull(count(question_id), 0) as question_cnt
from practice_record as pr
where year(pr.submit_time) = 2021
and pr.uid in (select ui.uid
from examination_info as ei 
join exam_record as er 
on ei.exam_id = er.exam_id
join user_info as ui 
on ui.uid = er.uid
where ei.tag = "SQL"
and ui.level = 7
and ei.difficulty = "hard"
group by er.uid
having avg(er.score) > 80)
group by pr.uid) as t2
on t1.uid = t2.uid

order by t1.exam_cnt, t2.question_cnt desc