如何找到连续数组的边界?

2021-12-13 00:00:00 sql-server-2008 sql-server gaps-and-islands

我有一个定义如下的表

CREATE TABLE mytable
  (
     id     INT IDENTITY(1, 1) PRIMARY KEY,
     number BIGINT,
     status INT
  )

和示例数据

INSERT INTO mytable
VALUES (100,0),
       (101,0),
       (102,0),
       (103,0),
       (104,1),
       (105,1),
       (106,0),
       (107,0),
       (1014,0),
       (1015,0),
       (1016,1),
       (1017,0)

仅查看 status = 0 的行，如何将 Number 值折叠到连续序列号的范围内并找到每个范围的开始和结束?

Looking only at the rows where status = 0 how can I collapse the Number values into ranges of contiguous sequential numbers and find the start and end of each range?

即对于示例数据，结果将是

i.e. For the example data the results would be 

         FROM      to 
Number    100      103
Number    106      107
Number    1014     1015
Number    1017     1017

推荐答案

正如评论中提到的，这是一个典型的间隙和孤岛问题.

As mentioned in the comments this is a classic gaps and islands problem.

Itzik Ben Gan 推广的一个解决方案是利用ROW_NUMBER() OVER (ORDER BY number) - number 在一个孤岛"内保持不变，不能出现在多个孤岛上的事实.>

A solution popularized by Itzik Ben Gan is to use the fact that ROW_NUMBER() OVER (ORDER BY number) - number remains constant within an "island" and cannot appear in multiple islands.

WITH T
     AS (SELECT ROW_NUMBER() OVER (ORDER BY number) - number AS Grp,
                number
         FROM   mytable
         WHERE  status = 0)
SELECT MIN(number) AS [From],
       MAX(number) AS [To]
FROM   T
GROUP  BY Grp
ORDER  BY MIN(number)

注意:如果不能保证 number 是唯一的，请将上面代码中的 ROW_NUMBER 替换为 DENSE_RANK.

NB: If number is not guaranteed to be unique replace ROW_NUMBER with DENSE_RANK in the code above.

相关文章