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

select distinct exam_id,duration,release_time
from(  select distinct exam_id,duration,release_time,
           sum(case when ranking_desc = 2 then time_cha
                    when ranking_asc = 2 then - time_cha else 0 end) as two_slow
       from  ( select exam_id,tag,duration,release_time,timestampdiff(minute,start_time,submit_time) as time_cha,
                  row_number() over(partition by exam_id order by timestampdiff(minute,start_time,submit_time) desc) ranking_desc,
                  row_number() over(partition by exam_id order by timestampdiff(minute,start_time,submit_time) asc) ranking_asc
                from examination_info a join exam_record b using(exam_id)
                where score is not null
             ) c
       group by exam_id
     ) d