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

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

https://www.nowcoder.com/practice/b1e2864271c14b63b0df9fc08b559166

select 
exam_id
,duration
,release_time
-- ,sum(big)
-- ,sum(small)
from
(
  select
t.exam_id
,duration
,release_time
,dif big
,0   small
from 
(
select
a.exam_id
,duration
,release_time
,timestampdiff(minute,start_time,submit_time) dif
-- ,row_number()over(partition by  a.exam_id order by timestampdiff(minute,start_time,submit_time) ) rn 
 ,row_number()over(partition by  a.exam_id order by timestampdiff(minute,start_time,submit_time) desc) rn1 
from exam_record a 
left join examination_info b on a.exam_id=b.exam_id
where submit_time is not null
) t
where rn1=2
union all
select
t.exam_id
,duration
,release_time
,0
,dif
from 
(
select
a.exam_id
,duration
,release_time
,timestampdiff(minute,start_time,submit_time) dif
,row_number()over(partition by  a.exam_id order by timestampdiff(minute,start_time,submit_time) ) rn 
,row_number()over(partition by  a.exam_id order by timestampdiff(minute,start_time,submit_time) desc) rn1 
from exam_record a 
left join examination_info b on a.exam_id=b.exam_id
where submit_time is not null
) t
where rn=2
  
) t1
group by exam_id
,duration
,release_time
having sum(big)-sum(small)>=max(duration)/2
order by exam_id desc



#MySQL#
全部评论

相关推荐

昨天 11:33
江南大学 Java
已经在暑假实习了 ,没有明确说有hc,纠结实习到八月份会不会有点影响秋招毕竟感觉今年好多提前批
程序员小白条:92的话准备提前批,其他没必要,没面试机会的,而且你要准备充分,尤其八股和算法题
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

更多
牛客网
牛客网在线编程
牛客网题解
牛客企业服务