SQL137 第二快/慢用时之差大于试卷时长一半的试卷

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

一、知识点

1、计算时间差timestampdiff()

格式：timestampdiff(时间格式，开始时间，结束时间)

时间格式：second - 秒；minute - 分钟；hour - 小时；day - 天；month - 月；year- 年。

2、窗口函数

是对where或group by 子句处理后的结果进行操作，原则上只能写在select子句中。

格式：<窗口函数>（） over (partition by <用于分组的列名>

                                                order by <用于排序的列名>)

窗口函数后面的括号不要忘记！！！

partition by <用于分组的列名>可以省略，省略就是不指定分组，只排序。

<窗口函数>有专用窗口函数（rank,dense_rank,row_rank等）和聚合窗口函数(sum,avg,count,max,min等)。

排序窗口函数的区别：

• rank() over() 1 1 1 4 4 6  (计数排名)
• dense_rank() over() 1 1 1 2 2 3 (对类别进行计数排名)
• row_number() over() 1 2 3 4 5 6 (唯一排名）

3、case 函数

简单case函数

case搜索函数

https://blog.csdn.net/Alian_1223/article/details/128253574?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522169649400316800213069998%2522%252C%2522scm%2522%253A%252220140713.130102334..%2522%257D&request_id=169649400316800213069998&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~baidu_landing_v2~default-2-128253574-null-null.142^v94^chatsearchT3_1&utm_term=my%20sql%20case&spm=1018.2226.3001.4187

二、题目分析

1、找到每份试卷的ID、限制时间、发布时间以及每份试卷被完成的耗时、耗时降序排名和升序排名。

根据开始时间和结束时间计算时间差。知识点：timestampdiff()

找到每张试卷完成时间的降序排名（排名为二是第二快）和升序排名（排名为二是第二慢）。知识点：row_number() over()窗口函数

连接表examination_info和exam_record。知识点：join...on...

查询出的表格记为table1

select b.exam_id,duration,release_time,
timestampdiff(minute,start_time,submit_time) as sjc,
row_number()over(partition by b.exam_id order by timestampdiff(minute,start_time,submit_time) desc) as rank1,
row_number()over(partition by b.exam_id order by timestampdiff(minute,start_time,submit_time) asc) as rank2
from exam_record as b join examination_info as a on a.exam_id=b.exam_id
where submit_time is not null

2、根据上面筛选出来的信息查询每份试卷的ID、限制时间、发布时间及第二快与第二慢的差值。

每张不同的试卷都会有一个数据，因此以试卷ID作为分组。知识点：group by

每份试卷的ID、限制时间、发布时间从table1直接查询。

发布时间及第二快与第二慢的差值：将试卷完成时间累加，当降序排名为2时加+时间差，当升序排名为2时-时间差，列名命名为sub。知识点：sum();case函数

查询出的表格记为table2

select exam_id,duration,release_time,
    sum(case when rank1=2 then sjc when rank2=2 then -sjc else 0 end) as sub
    from(
        select b.exam_id,duration,release_time,
        timestampdiff(minute,start_time,submit_time) as sjc,
        row_number()over(partition by b.exam_id order by timestampdiff(minute,start_time,submit_time) desc) as rank1,
        row_number()over(partition by b.exam_id order by timestampdiff(minute,start_time,submit_time) asc) as rank2
        from exam_record as b join examination_info as a on a.exam_id=b.exam_id
        where submit_time is not null
    )table1
    group by exam_id

3、从table2中查询每份试卷的ID、限制时间、发布时间。

将sub列大于等于限制时间一半的试卷ID作为筛选条件。知识点：where

输出按照试卷ID的降序排列。知识点：order by

完整代码：

select exam_id,duration,release_time
from (
    select exam_id,duration,release_time,
    sum(case when rank1=2 then sjc when rank2=2 then -sjc else 0 end) as sub
    from(
        select b.exam_id,duration,release_time,
        timestampdiff(minute,start_time,submit_time) as sjc,
        row_number()over(partition by b.exam_id order by timestampdiff(minute,start_time,submit_time) desc) as rank1,
        row_number()over(partition by b.exam_id order by timestampdiff(minute,start_time,submit_time) asc) as rank2
        from exam_record as b join examination_info as a on a.exam_id=b.exam_id
        where submit_time is not null
    )table1
    group by exam_id
)table2
where sub*2>=duration
order by exam_id desc

欢迎探讨。