SQL每日一题(20220323)希望得到0或1连续的区间的第一条记录

time丶sand

于 2022-05-20 08:36:11 发布

阅读量105

点赞数

分类专栏：每日一练文章标签： sql 数据库 database

本文链接：https://blog.csdn.net/weixin_44681259/article/details/124870036

版权

每日一练专栏收录该内容

119 篇文章 17 订阅

订阅专栏

在这里插入图片描述

select b.type, min(b.ltime) ltime
from (select a.*,
             cast(row_number() over (partition by a.type order by a.ltime) as signed) a,
             cast(row_number() over (order by a.ltime) as signed)                     b,
             cast(row_number() over (partition by a.type order by a.ltime) as signed) -
             cast(row_number() over (order by a.ltime) as signed)                     rn
      from f0323 a) b
group by b.type, b.rn
order by 2

SELECT type, ltime
FROM (SELECT *, RANK() over ( PARTITION BY (mn - rn) ORDER BY ltime ) sm
      FROM (SELECT *, ROW_NUMBER() over ( ORDER BY ltime ) mn, ROW_NUMBER() over ( PARTITION BY type ORDER BY ltime ) rn
            FROM f0323) t) n
WHERE sm = 1
ORDER BY ltime