题解 | #满足条件的用户的试卷完成数和题目练习数#
满足条件的用户的试卷完成数和题目练习数
https://www.nowcoder.com/practice/5c03f761b36046649ee71f05e1ceecbf
这道题实际可以拆解为两道类似的题目，只要理解了其中的一道，问题就解决了。其中一道是：请你找到高难度SQL试卷得分平均值大于80并且是7级的红名大佬，统计他们的2021年试卷总完成次数；
另一道是：请你找到高难度SQL试卷得分平均值大于80并且是7级的红名大佬，统计他们的2021年题目总练习次数。将上述两道题目分别查询出来后再进行join连接即可。
此题存在如下一些考察点：
多表连接、子查询、分组聚合，还包括一些常见函数的考察，包括ifnull函数、year()年份提取函数等，下面结合代码进行注解：
select
s1.uid,
exam_cnt,
ifnull(question_cnt, 0) // ④ 查询指标 ： 由于题目练习量可能为空，因此采用ifnull函数取练习量为空时为0
from
(
select
t1.uid uid,
count(distinct t2.exam_id) exam_cnt
from // ① 表连接
exam_record t1
left join examination_info t2 on t1.exam_id = t2.exam_id
left join user_info t3 on t1.uid = t3.uid
where
t1.uid in ( // ② 子查询：查询高难度SQL试卷得分平均值大于80并且是7级的红名大佬 select
t1.uid
from
exam_record t1
left join examination_info t2 on t1.exam_id = t2.exam_id
left join user_info t3 on t1.uid = t3.uid
where
level = 7
and difficulty = "hard"
and tag = "SQL"
group by
t1.uid
having
avg(score) > 80
)
and year(submit_time) = "2021" // ② 仅查询2021年的作答信息
group by // ③ 分组查询
t1.uid
) s1
************* 上述部分为第一道题目，下述为第二道题目 ******************
left join (
select
uid,
count(question_id) question_cnt
from
practice_record
where
uid in ( // ① 子查询：查询高难度SQL试卷得分平均值大于80并且是7级的红名大佬
select
a1.uid
from
exam_record a1
left join examination_info a2 on a1.exam_id = a2.exam_id
left join user_info a3 on a1.uid = a3.uid
where
level = 7
and difficulty = "hard"
and tag = "SQL"
group by
a1.uid
having
avg(score) > 80
)
and year(submit_time) = "2021" // ② 仅查询2021年的信息
group by // ③ 分组查询
uid
) s2 on s1.uid = s2.uid
order by
exam_cnt asc, // ⑤ 按作答问卷数量升序，按题目练习量降序
question_cnt desc
查看1道真题和解析