mysql窗口函数应用-----第二快慢用时之差大于试卷时长一半的试卷详解

题目地址第二快慢用时之差大于试卷时长一半的试卷_牛客题霸_牛客网 (nowcoder.com)

解题代码与解释：

select exam_id,duration,release_time
from 
    (select exam_id,duration,release_time,TIMESTAMPDIFF(minute,start_time,submit_time) as mm
    ,row_number() over(PARTITION BY exam_id order by TIMESTAMPDIFF(minute,start_time,submit_time)) as m1
    ,row_number() over(PARTITION BY exam_id order by TIMESTAMPDIFF(minute,start_time,submit_time) desc) as m2
    from exam_record e left join examination_info ei using(exam_id)
    where submit_time is not null) t
where m1=2 or m2=2
group by exam_id 
having max(mm)-min(mm)>duration*0.5
order by exam_id desc

1. 在子查询中，使用 TIMESTAMPDIFF(minute,start_time,submit_time) 计算每条记录的用时（以分钟为单位），
2. 使用 ROW_NUMBER() 函数计算出当前记录在考试记录中的排名（第几短与第几长）,其中m1为正序排列，m2为倒序排列。
3. 在主查询中，使用 WHERE 子句根据 m1 和 m2 的值，筛选出所有试卷考试时间排名为第二与倒数第二的记录。
4. 使用group by聚合后，在 having 中，使用 max(mm) - MIN(mm) 筛选从上一步的筛选出的基础上，进一步得出每张试卷考试时间第二与倒数第二之差是否大于考试规定时长的一半。(因为group by后此时剩下的是每张试卷的第二与倒数第二时间记录)
5. 最后使用order by排序输出