我有一个表格,其中包含以下定义
CREATE TABLE mytable
(
id INT IDENTITY(1,1) PRIMARY KEY,number BIGINT,status INT
)
和示例数据
INSERT INTO mytable VALUES (100,0),(101,(102,(103,(104,1),(105,(106,(107,(1014,(1015,(1016,(1017,0)
仅查看status = 0的行,如何将Number值折叠为连续序列号的范围并查找每个范围的开始和结束？
即对于示例数据,结果将是
FROM to Number 100 103 Number 106 107 Number 1014 1015 Number 1017 1017
解决方法
正如评论中所提到的,这是一个典型的差距和岛屿问题.
由Itzik Ben Gan推广的解决方案是使用ROW_NUMBER()OVER(ORDER BY number) – 数字在“岛”内保持不变并且不能出现在多个岛中的事实.
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)
注意：如果数字不能保证是唯一的,请在上面的代码中用DENSE_RANK代替ROW_NUMBER.
