--測(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',1
UNION ALL SELECT 'b',5
UNION ALL SELECT 'b',6
UNION ALL SELECT 'b',7
GO

--缺號(hào)分布查詢
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 --為每組編號(hào)補(bǔ)充查詢起始編號(hào)是否缺號(hào)的輔助記錄
?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ù)中,編號(hào)最大的記錄
?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
--*/

查詢?nèi)碧?hào)分布情況的示例.sql