自增列查询问题
我有一个表是自增主键列
如1，2，3，5，6，7，9，11，12
我现在要查出来所有不在自增主键列的所有数字
也就是要查出来以前删除的列：4，8，10
求SQL语句，谢谢！
[解决办法]
5.4.2 查询缺号分布情况的示例.sql
--测试数据
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 <b.col2 --过滤掉每组数据中,编号最大的记录
AND NOT EXISTS(
SELECT * FROM tb WHERE col1=a.col1 AND col2=a.col2+1)
ORDER BY a.col1,start_col2
/*--结果
col1 start_col2 end_col2
-------------- -------------- -----------
a 1 1
a 4 5
b 2 4
--*/
--借用临时表：create table tb(id int)insert into tb select 1insert into tb select 2insert into tb select 3insert into tb select 5insert into tb select 6insert into tb select 7insert into tb select 9insert into tb select 11insert into tb select 12declare @sql varchar(1000)select @sql='select top '+cast(max(id) as varchar)+' bh=identity(int,1,1) into ## from syscolumns' from tbexec(@sql)select bh from ## where not exists(select 1 from tb where bh=id)
