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

问题描述

我有一个具有以下定义的表

I have a table with the following definition

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屋！