SQL 获取连续编号中断号的最小值

问题描述；返回最小的缺失数,要求一定是正数.IF OBJECT_ID('dbo.T1') IS NOT NULL  DROP TABLE dbo.T1;GOCREATE TABLE dbo.T1(  keycol  INT         NOT NULL PRIMARY KEY CHECK(keycol > 0),  datacol VARCHAR(10) NOT NULL);INSERT INTO dbo.T1(keycol, datacol) VALUES(3, 'a');INSERT INTO dbo.T1(keycol, datacol) VALUES(4, 'b');INSERT INTO dbo.T1(keycol, datacol) VALUES(6, 'c');INSERT INTO dbo.T1(keycol, datacol) VALUES(7, 'd');--方法1：case when ....endselect case when not exists(select * from T1 where keycol=1) then 1else (select MIN(keycol) from T1 a  where not exists(select * from T1 where keycol=a.keycol+1))+1 end--方法2;COALESCE(a,b)函数--具体翻MSDN吧select coalesce(MIN(keycol+1),1)from T1 awhere not exists(select * from T1 where keycol=a.keycol+1) and exists(select * from T1 where keycol=1)--这个EXISTS如果为NULL where 条件为假，那么MIN(KEYCOL+1)得到一个NULL,那么函数取第二个参数1 --方法3：临时表(这里的临时表产生方法我在后面会说，当然我之前第一次的学习笔记也有，有兴趣的可以去翻翻)select top 1 Nfrom T1 right join  NUM on T1.keycol=NUM.N--这里的NUN表是一个从1-1000000的表where   N<=(select MAX(keycol) from T1 ) and keycol is null--方法4：表之间的OUTER JOINSELECT case when not exists(select * from T1 where keycol=1) then 1else (select MIN(A.keycol + 1)FROM dbo.T1 AS A  LEFT OUTER JOIN dbo.T1 AS B    ON B.keycol = A.keycol + 1WHERE B.keycol IS NULL)end