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

-- 方法一
/*
with 
t1 as
(select er.exam_id,
    ei.duration,
    ei.release_time,
    timestampdiff(second,er.start_time,er.submit_time) as time_diff,
    row_number() over(partition by er.exam_id order by 
        timestampdiff(second,er.start_time,er.submit_time) desc) as rank_desc,
    row_number() over(partition by er.exam_id order by 
        timestampdiff(second,er.start_time,er.submit_time) asc) as rank_asc
from exam_record er
left join examination_info ei
on er.exam_id = ei.exam_id
where er.submit_time is not null)
SELECT s1.exam_id,s1.duration,s1.release_time from 
(select exam_id,time_diff,release_time,duration from t1 where rank_desc=2) s1
inner join 
(select exam_id,time_diff,release_time,duration from t1 where rank_asc=2)  s2
on s1.exam_id = s2.exam_id
where s1.time_diff - s2.time_diff >= s1.duration*60/2
order by s1.exam_id desc;
*/

-- 方法二
SELECT exam_id ,duration, release_time 
from
	(SELECT exam_id ,duration, release_time ,
		sum(case when rank_desc = 2 then time_diff 
		when rank_asc = 2 then -time_diff else 0 end) as rank_time_diff
	from
		(select er.exam_id,
		    ei.duration,
		    ei.release_time,
		    timestampdiff(second,er.start_time,er.submit_time) as time_diff,
		    row_number() over(partition by er.exam_id order by 
		        timestampdiff(second,er.start_time,er.submit_time) desc) as rank_desc,
		    row_number() over(partition by er.exam_id order by 
		        timestampdiff(second,er.start_time,er.submit_time) asc) as rank_asc
		from exam_record er
		left join examination_info ei
		on er.exam_id = ei.exam_id
		where er.submit_time is not null
		) as 排名表
	group by exam_id
	) as 结果表
where rank_time_diff > duration *60 /2
order by exam_id desc;