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

select exam_id,duration,release_time from examination_info where exam_id in (
	SELECT exam_id from (
	SELECT exam_id,
	duration,
	sum(case when rk1=2 then time1 else 0 end)-sum(case when rk2=2 then time1 else 0 end) as count1 from (
	
	SELECT exam_id,time1,duration,
	ROW_NUMBER() over(partition by exam_id order by time1 desc) as rk1,
	ROW_NUMBER() over(partition by exam_id order by time1 asc) as rk2
	from (
	select er.*,timestampdiff(second,start_time,submit_time) as time1,ei.duration,ei.release_time from exam_record er join examination_info ei 
on er.exam_id=ei.exam_id
	
	)t1
	)t2 group by exam_id
	)t3
	 where count1>duration*30) order by exam_id desc;
	 这题目实在是有点困难