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

select ei.exam_id,duration,release_time
from (
    select *
    ,row_number() over(partition by er.exam_id order by timeuse ASC) as asc_rank
    ,row_number() over(partition by er.exam_id order by timeuse DESC) as desc_rank
from (
    select *, round(timestampdiff(second,start_time,submit_time)/60,1) as timeuse from exam_record
) as er
) as tmp
left join examination_info as ei
using(exam_id)
where if(desc_rank = 2,timeuse,0)-if(asc_rank = 2,timeuse,0) > 0.5*duration
order by ei.exam_id desc

新手思路，感觉比上一题简单，就是搜了一下timestampdiff这个函数