如何找到连续数组的边界? [英] How to find the boundaries of groups of contiguous sequential numbers?

问题描述

我有一个定义如下的表

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.

这篇关于如何找到连续数组的边界?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！