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

select exam_id,duration,release_time
from examination_info
where exam_id in (
		select exam_id
		from (
					select exam_id,spend_time_fast
					from (
							select exam_id,spend_time as spend_time_fast,
										 row_number() over(partition by exam_id order by spend_time asc) as ranking_fast,
										 row_number() over(partition by exam_id order by spend_time desc) as ranking_slow
							from (
								select exam_id,
											 timestampdiff(minute,start_time,submit_time) as spend_time
								from exam_record
										) as t
								) as t1
					where ranking_fast=2
					) as table1
		left join (
				select exam_id,spend_time as spend_time_slow
				from (
						select exam_id,spend_time,
									 row_number() over(partition by exam_id order by spend_time asc) as ranking_fast,
									 row_number() over(partition by exam_id order by spend_time desc) as ranking_slow
						from (
							select exam_id,
										 timestampdiff(minute,start_time,submit_time) as spend_time
							from exam_record
									) as t
							) as t1
				where ranking_slow=2
							) as t2 using(exam_id)
		left join examination_info using(exam_id)
		where spend_time_slow-spend_time_fast>duration/2
		)
order by exam_id desc