--測(cè)試數(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',3
UNION ALL SELECT 'b',5
UNION ALL SELECT 'b',6
UNION ALL SELECT 'b',7
GO

--已用編號(hào)分布查詢
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)
/*--結(jié)果
col1?????? start_col2? end_col2???
-------------- -------------- -----------
a????????? 2?????????? 3
a????????? 6?????????? 8
b????????? 3?????????? 3
b????????? 5?????????? 7
--*/

查詢已用編號(hào)分布情況的示例（子查詢法).sql