--測試數(shù)據(jù)
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',2
UNION ALL SELECT 'a',3
UNION ALL SELECT 'a',6
UNION ALL SELECT 'a',7
UNION ALL SELECT 'a',8
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',5
UNION ALL SELECT 'b',6
UNION ALL SELECT 'b',7
GO

--缺號分布查詢
SELECT a.col1,start_col2=a.col2+1,
	end_col2=(
		SELECT MIN(col2) FROM tb aa
		WHERE col1=a.col1 AND col2>a.col2 
			AND NOT EXISTS(
				SELECT * FROM tb WHERE col1=aa.col1 AND col2=aa.col2-1))
		-1
FROM(
	SELECT col1,col2 FROM tb
	UNION ALL --為每組編號補充查詢起始編號是否缺號的輔助記錄
	SELECT DISTINCT col1,0 FROM tb
)a,(SELECT col1,col2=MAX(col2) FROM tb GROUP BY col1)b
WHERE a.col1=b.col1 AND a.col2<b.col2 --過濾掉每組數(shù)據(jù)中,編號最大的記錄
	AND NOT EXISTS(
		SELECT * FROM tb WHERE col1=a.col1 AND col2=a.col2+1)
ORDER BY a.col1,start_col2
/*--結(jié)果
col1       start_col2  end_col2    
-------------- -------------- ----------- 
a          1           1
a          4           5
b          2           4
--*/