题解 | #第二快/慢用时之差大于试卷时长一半的试卷#

分享我的曲折思路历程：

困难① 由于只想到用 一次 窗口函数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;