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

明确题意：

找到高难度SQL试卷得分平均值大于80并且是7级红名大佬的2021年试卷完成数和题目练习数

问题拆解：

• 本题主要是考察知识点：join、case when 、group by等，重点是对逻辑的理解！！！
• 先查出平均值大于80并且是7级红名大佬的的uid，得到t1
• t1分别与t2、t3关联，注意要用left join，因为有些uid可能没做某个试卷或练习，也要保留记录
• 不能在关联后统一卡where条件，因为因为有些uid可能没做某个试卷或练习，所以submit_time可能为NULL，使用where and后的结果则不会含有submit_time为NULL的记录，会使结果变少，比如uid1003!!!使用where or的结果可能会使结果变多，下面有错误的代码结果，见2，3！！所以需要在count中用case when卡submit_time !!
• count(distinct )时，要以id区分，不能以exam_id区分，因为存在一个uid可能对同一个试卷或练习做过多次！！比如1006对8003做过多次！！
• 最后分组排序

过程：

代码实现：

select 
t1.uid,
-- count(distinct case when year(t2.submit_time) = '2021' then t2.exam_id else null end) as exam_cnt, -- 不对
-- count(distinct case when year(t3.submit_time) = '2021' then t3.question_id else null end) as question_cnt -- 不对
count(distinct case when year(t2.submit_time) = '2021' then t2.id else null end) as exam_cnt, -- 正确	
count(distinct case when year(t3.submit_time) = '2021' then t3.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 
) t1 
 -- join -- 不能用join
left join 
exam_record t2 
on t1.uid = t2.uid 
 -- join -- 不能用join
left join 
practice_record t3 
on t1.uid = t3.uid 
-- where year(t2.submit_time) = '2021' and  year(t3.submit_time) = '2021' -- 不能在这里统一用where卡条件！！
group by t1.uid
order by exam_cnt asc , question_cnt desc ;
-- 结果按试卷完成数升序，按题目练习数降序

select 
t1.uid,
count(distinct t2.id) as exam_cnt,
count(distinct t3.id) 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 
) t1 
 -- join -- 不能用join
left join 
exam_record t2 
on t1.uid = t2.uid 
 -- join -- 不能用join
left join 
practice_record t3 
on t1.uid = t3.uid 
-- where year(t2.submit_time) = '2021' and  year(t3.submit_time) = '2021' -- 结果不对
-- where year(t2.submit_time) = '2021'&nbs***bsp; year(t3.submit_time) = '2021' -- 结果不对
group by t1.uid
order by exam_cnt asc , question_cnt desc ;
-- 结果按试卷完成数升序，按题目练习数降序