我有一个表user,
id IDENTITY (1, 1)
username [nvarchar] (30) NULL 内里已经有多条记录,期间是删除了多条记录,所以id已经不是连续的了,我的要求是取得已经删除记录的最小id,不要用循环判断的方法,也不要告诉我在删除时处理保存最小ID
id IDENTITY (1, 1)
username [nvarchar] (30) NULL 内里已经有多条记录,期间是删除了多条记录,所以id已经不是连续的了,我的要求是取得已经删除记录的最小id,不要用循环判断的方法,也不要告诉我在删除时处理保存最小ID
from user a
where not exists(select 1 from user where a.id > id)
select min(id) + 1
from user a
where not exists(select 1 from user where id = a.id + 1)
min(id) + 1
from
[user] t
where
not exists(select 1 from user where id = t.id + 1)
create table [user](id int identity(1,1),username nvarchar(30))
set identity_insert [user] on
insert into [user](id,username) select 1,'张三'
insert into [user](id,username) select 4,'李四'
insert into [user](id,username) select 8,'王五'
insert into [user](id,username) select 9,'赵六'
set identity_insert [user] off
go--查询已经删除最小的iD
select min(isnull(b.id,a.id+1)) from [user] a left join [user] b
on a.id=b.id-1--结果:
-----------
2
from
(
select order_id=(select count(1) from [user] where id<=t.id),*
from [user] t
) r
where order_id <> id
set identity_insert [user] on
insert into [user](id,username) select 1,'张三'
insert into [user](id,username) select 4,'李四'
insert into [user](id,username) select 8,'王五'
insert into [user](id,username) select 9,'赵六'
set identity_insert [user] offselect MIN(id)+1
from [user] k
where not exists(select * from [user] where k.id+1=id)--or
select top 1 rn as id
from (select rn=id-ROW_NUMBER()over(order by id ),id from [user])k
where rn<>0
order by id
忘记考虑1的情况了 --问题描述;返回最小的缺失数,要求一定是正数.
/*
*************************************
* T-MAC 小编 *
* -->努力成长中 *
* -->梦想DBA *
*************************************
*/
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
*/
IF(SELECT MIN(ID) FROM [user])>1
SELECT 1
SELECT MIN(ID) +1
SELECT [user] A
WHERE NOT EXISTS(SELECT 1 FROM [user] WHERE ID= A.ID+1)
ELSE
IF(SELECT MIN(ID) FROM [user])>1
SELECT 1
ELSE
SELECT MIN(ID) +1
SELECT [user] A
WHERE NOT EXISTS(SELECT 1 FROM [user] WHERE ID= A.ID+1)
sorry,应该是这样的,else放错地方了,o(∩_∩)o...