主页 / dba / 问题 / 159767
Accepted
Jason
Asked: 2017-01-03 12:57:40 +0800 CST

用于查找连续时间段并生成 id 的优雅代码

  • 772

我有一个包含两个字段的表：时间和值，它们都是整数，我想找到值大于 0 的连续周期，然后用连续整数标记“值> 0”周期。

例如，如果我有这样的输入表：

输入表

我想要这样的输出表：

输出表

使用三个 CTE 和两个 row_number() 函数，我能够做到这一点。但是我觉得查询太麻烦了。有没有人有更优雅的代码来做到这一点？

我正在使用 sql server 2016 开发人员版。

这是我的代码：

CREATE TABLE #test1(
    [time] [int] NULL,
    [value] [int] NULL
)

insert into #test1
values(1,0),(2,0),(3,1),(4,0)
    ,(5,1),(6,2),(7,0),(8,0)
,(9,1),(10,2),(11,3),(12,0)
,(13,0),(14,0),(15,1),(16,0);

;with a1 as
(select *, [time] -  row_number() over (order by [time]) as group_num
    from #test1
where value>0),
a2 as 
(select distinct group_num from a1
),
a3 as
(select group_num, row_number() over (order by group_num) as group_id
from a2)
select a1.*, a3.group_id
    from a1 left join a3
on a1.group_num = a3.group_num
sql-server t-sql

1 个回答

  1. Best Answer

    您只需要申请DENSE_RANK第一个 CTE 的结果 - Demo

    WITH T
     AS (SELECT *,
                [time] - row_number() OVER (ORDER BY [time]) AS group_num
         FROM   #test1
         WHERE  value > 0)
SELECT *,
       DENSE_RANK() OVER (ORDER BY group_num) AS group_id
FROM   T
    • 5

相关问题