61.查询应用编号分布情况的案例（子表询法）

--测试数据
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',3
UNION ALL SELECT 'b',5
UNION ALL SELECT 'b',6
UNION ALL SELECT 'b',7
GO

--已用编号分布查询
SELECT col1,start_col2=col2,
	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))
FROM tb a
WHERE NOT EXISTS(
	SELECT * FROM tb WHERE col1=a.col1 and col2=a.col2-1)
/*--结果
col1       start_col2  end_col2    
-------------- -------------- ----------- 
a          2           3
a          6           8
b          3           3
b          5           7
--*/