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

select 
exam_id
,duration
,release_time
-- ,sum(big)
-- ,sum(small)
from
(
  select
t.exam_id
,duration
,release_time
,dif big
,0   small
from 
(
select
a.exam_id
,duration
,release_time
,timestampdiff(minute,start_time,submit_time) dif
-- ,row_number()over(partition by  a.exam_id order by timestampdiff(minute,start_time,submit_time) ) rn 
 ,row_number()over(partition by  a.exam_id order by timestampdiff(minute,start_time,submit_time) desc) rn1 
from exam_record a 
left join examination_info b on a.exam_id=b.exam_id
where submit_time is not null
) t
where rn1=2
union all
select
t.exam_id
,duration
,release_time
,0
,dif
from 
(
select
a.exam_id
,duration
,release_time
,timestampdiff(minute,start_time,submit_time) dif
,row_number()over(partition by  a.exam_id order by timestampdiff(minute,start_time,submit_time) ) rn 
,row_number()over(partition by  a.exam_id order by timestampdiff(minute,start_time,submit_time) desc) rn1 
from exam_record a 
left join examination_info b on a.exam_id=b.exam_id
where submit_time is not null
) t
where rn=2
  
) t1
group by exam_id
,duration
,release_time
having sum(big)-sum(small)>=max(duration)/2
#MySQL#