首先我们的基础的数据如下：
USE tempdb;
GO
IF OBJECT_ID('dbo.T1') IS NOT NULL
DROP TABLE dbo.T1
GO
CREATE TABLE dbo.T1(col1 INT NOT NULL PRIMARY KEY);
INSERT INTO dbo.T1(col1) VALUES(1);
INSERT INTO dbo.T1(col1) VALUES(2);
INSERT INTO dbo.T1(col1) VALUES(3);
INSERT INTO dbo.T1(col1) VALUES(100);
INSERT INTO dbo.T1(col1) VALUES(101);
INSERT INTO dbo.T1(col1) VALUES(103);
INSERT INTO dbo.T1(col1) VALUES(104);
INSERT INTO dbo.T1(col1) VALUES(105);
INSERT INTO dbo.T1(col1) VALUES(106);
问题1：找到丢失数字范围，如根据上面数据应返回
4~99 102 ~ 102
一种方法：
SELECT col1 + 1 AS start_range,
(SELECT MIN(col1) FROM dbo.T1 AS B
WHERE B.col1 > A.col1) - 1 AS end_range
FROM dbo.T1 AS A
WHERE NOT EXISTS
(SELECT * FROM dbo.T1 AS B
WHERE B.col1 = A.col1 + 1)
AND col1 < (SELECT MAX(col1) FROM dbo.T1);
更易懂的：
SELECT cur + 1 AS start_range, nxt - 1 AS end_range
FROM (SELECT col1 AS cur,
(SELECT MIN(col1) FROM dbo.T1 AS B
WHERE B.col1 > A.col1) AS nxt
FROM dbo.T1 AS A) AS D
WHERE nxt - cur > 1;
问题2：找到所有的间隙(全体丢失的数字)
SELECT n
FROM dbo.Nums
WHERE n BETWEEN (SELECT MIN(col1) FROM dbo.T1)
AND (SELECT MAX(col1) FROM dbo.T1)
AND NOT EXISTS(SELECT * FROM dbo.T1 WHERE col1 = n);
问题3：查找存在的范围
SELECT MIN(col1) AS start_range, MAX(col1) AS end_range
FROM (SELECT col1,
(SELECT MIN(col1) FROM dbo.T1 AS B
WHERE B.col1 >= A.col1
AND NOT EXISTS
(SELECT * FROM dbo.T1 AS C
WHERE B.col1 = C.col1 - 1)) AS grp
FROM dbo.T1 AS A) AS D
GROUP BY grp;
简单方法：
SELECT MIN(col1) AS start_range, MAX(col1) AS end_range
FROM (SELECT col1, col1 - ROW_NUMBER() OVER(ORDER BY col1) as grp
FROM dbo.T1 ) AS D
GROUP BY grp;