62.查询缺号分布情况的案例

--测试数据
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