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

select exam_id,duration,release_time from
(select t2.exam_id,
t2.duration,
t2.release_time,
timestampdiff(minute,start_time,submit_time) cnt,
dense_rank() over(partition by exam_id order by timestampdiff(second,start_time,submit_time)) as rank_asc,
dense_rank() over(partition by exam_id order by  timestampdiff(second,start_time,submit_time) desc ) as rank_desc
from exam_record t1 join
examination_info t2 on t1.exam_id = t2.exam_id
where submit_time is not null) as temp1
group by exam_id 
having sum(if (rank_desc=2,cnt,0)) -sum(if (rank_asc=2,cnt,0)) >=duration/2
order by exam_id desc

• 这里最巧妙的是第12行。
• from之后使用子查询。