题解 | #第二快/慢用时之差大于试卷时长一半的试卷#
第二快/慢用时之差大于试卷时长一半的试卷
https://www.nowcoder.com/practice/b1e2864271c14b63b0df9fc08b559166
分享我的曲折思路历程：
困难① 由于只想到用 一次 窗口函数rank() over()获得 exam用时(submit_time-start_time)/100 as ut 降序排名结果，导致很难取出第二快的用时；另一个窗口函数nth_value完全不知道。【这里我直接用(submit_time-start_time)/100，没用timestampdiff】
②如何取出第二快和第二慢的用时，没想到用 sum()+if()/case when，只想到用if()/case when 区别就是后面是多行结果（比如符合第一快的用时，其他为null/0），前面结果只有一行。【都有0了，还没想到sum()，哎】
正确思路就清晰很多了
with t as( #创建t储存exam_id,duration,release_time,ut,两次排序结果 ut是用时
select
t.exam_id as exam_id,duration,release_time,ut,
rank() over(partition by t.exam_id order by t.ut desc ) as ranking_desc,#两次排序结果
rank() over(partition by t.exam_id order by t.ut asc ) as ranking_asc
from
(
select exam_id,(submit_time-start_time)/100 as ut
from exam_record
where submit_time is not null #剔除null值
)t left join
(
select exam_id,duration,release_time
from examination_info
)t1 on t.exam_id=t1.exam_id
)
,t1 as( #创建t1储存exam_id,duration,release_time，第二快asc_2和第二慢desc_2的用时
select exam_id,duration,release_time,
sum(if(ranking_desc=2,ut,0)) as desc_2,
sum(if(ranking_asc=2,ut,0)) as asc_2
from t
group by exam_id
)
select exam_id,duration,release_time
from t1
where desc_2-asc_2>duration/2
order by exam_id desc;
