27,579
社区成员
发帖
与我相关
我的任务
分享
还可以
SELECT *,(SELECT COUNT(1) FROM TB WHERE SN<=T.SN)RN FROM TB T
WHERE SN<>(SELECT COUNT(1) FROM TB WHERE SN<=T.SN)
DECLARE @RN INT
SET @RN =(SELECT COUNT(1) FROM TB)
SELECT ID=IDENTITY(INT,1,1) INTO #T FROM SYSCOLUMNS A,SYSCOLUMNS B
SELECT * FROM TB WHERE SN NOT IN(SELECT ID FROM #T WHERE ID<=@RN)
IF OBJECT_ID('dbo.T1') IS NOT NULL
DROP TABLE dbo.T1;
GO
CREATE 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 ....end
select
case when not exists(select * from T1 where keycol=1) then 1
else (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 a
where 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 N
from 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 JOIN
SELECT case when not exists(select * from T1 where keycol=1) then 1
else (select MIN(A.keycol + 1)
FROM dbo.T1 AS A
LEFT OUTER JOIN dbo.T1 AS B
ON B.keycol = A.keycol + 1
WHERE B.keycol IS NULL)end
/*
N
-----------
1
*/