--测试数据
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 id=IDENTITY(int),col1,col2 INTO #1 FROM tb a
WHERE NOT EXISTS(
SELECT * FROM tb WHERE col1=a.col1 AND col2=a.col2-1)
SELECT id=IDENTITY(int),col2 INTO #2 FROM tb a
WHERE NOT EXISTS(
SELECT * FROM tb WHERE col1=a.col1 AND col2=a.col2+1)
SELECT a.col1,start_col2=a.col2,end_col2=b.col2
FROM #1 a,#2 b
WHERE a.id=b.id
DROP TABLE #1,#2
/*--结果
col1 start_col2 end_col2
-------------- -------------- -----------
a 2 3
a 6 8
b 3 3
b 5 7
--*/
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 id=IDENTITY(int),col1,col2 INTO #1 FROM tb a
WHERE NOT EXISTS(
SELECT * FROM tb WHERE col1=a.col1 AND col2=a.col2-1)
SELECT id=IDENTITY(int),col2 INTO #2 FROM tb a
WHERE NOT EXISTS(
SELECT * FROM tb WHERE col1=a.col1 AND col2=a.col2+1)
SELECT a.col1,start_col2=a.col2,end_col2=b.col2
FROM #1 a,#2 b
WHERE a.id=b.id
DROP TABLE #1,#2
/*--结果
col1 start_col2 end_col2
-------------- -------------- -----------
a 2 3
a 6 8
b 3 3
b 5 7
--*/