题解 | #检索顾客名称并且排序#

题目：两个表，找到第二快和第二慢用时之差大于试卷时长的一半的试卷信息，按试卷ID降序排序。

思路：

1. 找到第二快和第二慢用时，隐含的意思就是要按试卷exam_id分组，用到窗口函数NTH_VALUE(有序列,n) over()[找到有序列的第n行]；用时是提交时间-开始时间，用到了timestampdiff(精确单位，小时间，大时间)，这里精确到秒second
2. 将上述查询建立为子查询(注意要给子查询命名)，父查询查2快-2慢>试卷时长的一半（秒的话*60)
3. 排序：按试卷ID降序

select a.exam_id,duration,release_time
from (
SELECT distinct ei.exam_id,ei.duration,ei.release_time,
    NTH_VALUE(TIMESTAMPDIFF(second,start_time,submit_time),2) 
        over(partition by ei.exam_id
            order by TIMESTAMPDIFF(second,start_time,submit_time)) as kuai,
    NTH_VALUE(TIMESTAMPDIFF(second,start_time,submit_time),2) 
        over(partition by ei.exam_id
            order by TIMESTAMPDIFF(second,start_time,submit_time) desc) as man
FROM examination_info ei join exam_record er using(exam_id)
) a
where man-kuai>=duration*60/2
order by a.exam_id desc;