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

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

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#
全部评论

相关推荐

06-14 19:09
门头沟学院 Java
darius_:给制造业搞的,什么物料管理生产管理,设备管理点检,最最关键的就是一堆报表看板。个人觉得没啥技术含量都是些基本的crud,但是业务很繁琐那种
点赞 评论 收藏
分享
06-15 18:44
黄淮学院 Java
Lynn012:如果是居民楼还是算了吧,看着有点野呢
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

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