select a.noid+1 as noid from 单据表 a left join 单据表 b on b.noid=a.noid+1 where b.noid is null
DECLARE @t TABLE(ID int IDENTITY,b bit) INSERT @t(b) SELECT TOP 8000 0 FROM syscolumns a,syscolumns b select c.id from (select a.ID,b.noid from @t left outer join tblA b on a.ID=b.noid) c where c.noid is null
create table tblA(noid int,fQty int) insert into tblA select 1,2000 union all select 2,90 union all select 3,200 union all select 5,500 union all select 6,10 union all select 8,20 declare @max int declare @sql varchar(1000) select @max=max(noid) from tblA select @sql='select top '+rtrim(@max)+' identity(int,1,1) as id into tblB from sysobjects a,sysobjects b' exec(@sql) select id from tblB where not exists(select 1 from tblA where tblA.noid=tblB.id)
Create table T(NoID int ,Qty int)insert into T(NoID,Qty) select 1,2000 union all select 2,90 union all select 3,200 union all select 5,500 union all select 6,10 union all select 8,20 select top 8 NoID=identity(int,1,1) into # from sysobjects a,sysobjects bselect NoID from # A where not exists(select 1 from T where NoID = A.NoID)drop table T drop table #
declare @t table(noid int,fqty int) insert into @t select 1,2000 union all select 2,90 union all select 3,200 union all select 5,500 union all select 6,10 union all select 8,20select noid+1 from @t t where not exists (select 1 from @t where noid=t.noid+1 ) and noid<(select max(noid) from @t)4 7(所影响的行数为 2 行)
CSDNM(决定不当CSDN经理了) 写的对于只间连续缺多个数字就不能适应了
declare @maxid int select @maxid=max(noid) from tblAset rowcount @maxidselect id=identity(bigint,1,1) into #t from sysobjects a,sysobjects b,sysobjects c select id from #t a left join tblA b on a.id=b.noid where b.noid is nulldrop table #t
create table tblA(noid int, fQty int) insert into tblA select 1, 2000 union all select 2, 90 union all select 3, 200 union all select 5, 500 union all select 6, 10 union all select 8, 20 create table #t(no int) declare @min int ,@max int select @min=min(noid) from tbla select @max=max(noid) from tbla While(@min<=@max) begin insert into #t values(@min) set @min=@min+1 endselect * from #t where no not in(select noid from tbla)drop table #t
--测试环境 create table tblA(noid int,fQty int) goinsert into tblA select 1,2000 union all select 2,90 union all select 3,200 union all select 5,500 union all select 6,10 union all select 8,20 declare @maxid int select @maxid=max(noid) from tblAset rowcount @maxidselect @maxid select id=identity(bigint,1,1) into #t from sysobjects a,sysobjects b,sysobjects c select id from #t a left join tblA b on a.id=b.noid where b.noid is nulldrop table #t --结果: id -------------------- 4 7(所影响的行数为 2 行)
hrb2008的简单点 DECLARE @t TABLE(ID int IDENTITY,b bit) INSERT @t(b) SELECT TOP 8000 0 FROM syscolumns a,syscolumns b select c.id from (select a.ID,b.noid from @t left outer join tblA b on a.ID=b.noid) c where c.noid is null
编号 姓名 1 张山 3 历史 4 王无 6 李强 桥三 大兵我想对没有编号的进行编号的自动处理,但是已经有编号的不要进行修改。 如果发现有断号的就将号码分配给没有编号的。 结果如下: 编号 姓名 1 张山 3 历史 4 王无 6 李强 2 桥三 5 大兵 遍历记录,如果id为空,则找出最小未使用id,然后update找最小未使用id如下: select (case when exists (select 1 from test where id=1) then min(id+1) else 1 end) from test where id not in(select id-1 from test)
题目: 现在数据库由于需要要自己定义唯一编号,唯一编号由3个数字组成。这三个数字我存储为nvarchar. 由于唯一编号在使用中不可能超过999条记录,所以3个足够用。但是碰到一个问题.第一次使用时添加记录可以001,002,003...这样顺序添加. 当对这些记录进行删除之后再次添加.要核对输入的记录是否重复。这样非常被动。 每次添加记录都要验证该编号是否存在。设计这样的数据库,实在是没辙. 现在有没有办法能够自己得出一个在999内的未使用的编号呢。 非常感谢。用存储过程亦可。我有想过遍历,但是效率不高。希望有更简洁的方式。--1、没有数据的情况 if object_id('pubs..t') is not null drop table t gocreate table t(id char(3))select (case when exists (select 1 from t where cast(id as int)=1) then right('00' + rtrim(cast(min(id + 1) as char(3))) , 3) else '001' end) as 最小数据 from t where id not in(select id - 1 from t)drop table t--结果 最小数据 ----- 001(所影响的行数为 1 行) --2、有数据且连号。 if object_id('pubs..t') is not null drop table t gocreate table t(id char(3))insert into t(id) values('001') insert into t(id) values('002') insert into t(id) values('003')select (case when exists (select 1 from t where cast(id as int)=1) then right('00' + rtrim(cast(min(id + 1) as char(3))) , 3) else '001' end) as 最小数据 from t where id not in(select id - 1 from t)drop table t--结果 最小数据 ----- 004(所影响的行数为 1 行) --3、有数据但断号 if object_id('pubs..t') is not null drop table t gocreate table t(id char(3))insert into t(id) values('001') insert into t(id) values('002') insert into t(id) values('004')select (case when exists (select 1 from t where cast(id as int)=1) then right('00' + rtrim(cast(min(id + 1) as char(3))) , 3) else '001' end) as 最小数据 from t where id not in(select id - 1 from t)drop table t--结果 最小数据 ----- 003(所影响的行数为 1 行)
id1 id2 ----------- ----------- 2 2 4 5 ////////declare @T table (id int) insert into @T values(1) insert into @T values(3) insert into @T values(6) insert into @T values(7) select id+1 as id1,id+s-1 as id2 from ( select a.id as id, min(b.id-a.id) as S from @T a , @T b where a.id<b.id-1 group by a.id ) as t
改了一下 declare @T table (id int) insert into @T values(1) insert into @T values(3) insert into @T values(6) insert into @T values(7) insert into @T values(8) insert into @T values(9) insert into @T values(11) insert into @T values(12) insert into @T values(13)select id+1 as id1,id+s-1 as id2 from ( select a.id as id, min(b.id-a.id) as S from @T a , @T b where a.id<b.id group by a.id ) as t where s>1
create table tb (noid int,fqty int)insert tb select 1 , 2000 union all select 2 , 90 union all select 3 , 200 union all select 5 , 500 union all select 6 , 10 union all select 8 , 20
select a.noid+1 from ( select noid from tb union all select 0)a,(select max(noid) maxnoid from tb) b where not exists(select 1 from tb where noid=a.noid+1) and a.noid<b.maxnoid
这个人写的就很不错了,效率最高,唯一的问题就是随着时间推移,如果流水号表很大了,则IO会逐渐变的很大CSDNM(决定不当CSDN经理了) select a.noid+1 as noid from 单据表 a left join 单据表 b on b.noid=a.noid+1 where b.noid is null
from 单据表 a
left join 单据表 b
on b.noid=a.noid+1
where b.noid is null
INSERT @t(b) SELECT TOP 8000 0 FROM syscolumns a,syscolumns b
select c.id from
(select a.ID,b.noid from @t left outer join tblA b on a.ID=b.noid)
c where c.noid is null
insert into tblA
select 1,2000
union all select 2,90
union all select 3,200
union all select 5,500
union all select 6,10
union all select 8,20
declare @max int
declare @sql varchar(1000)
select @max=max(noid) from tblA
select @sql='select top '+rtrim(@max)+' identity(int,1,1) as id into tblB from sysobjects a,sysobjects b'
exec(@sql)
select id from tblB where not exists(select 1 from tblA where tblA.noid=tblB.id)
select 1,2000
union all
select 2,90
union all
select 3,200
union all
select 5,500
union all
select 6,10
union all
select 8,20
select top 8 NoID=identity(int,1,1)
into #
from sysobjects a,sysobjects bselect NoID
from # A
where not exists(select 1 from T where NoID = A.NoID)drop table T
drop table #
insert into @t select 1,2000
union all select 2,90
union all select 3,200
union all select 5,500
union all select 6,10
union all select 8,20select noid+1 from @t t where not exists (select 1 from @t where noid=t.noid+1 )
and noid<(select max(noid) from @t)4
7(所影响的行数为 2 行)
select @maxid=max(noid) from tblAset rowcount @maxidselect id=identity(bigint,1,1) into #t from sysobjects a,sysobjects b,sysobjects c
select id from #t a left join tblA b on a.id=b.noid where b.noid is nulldrop table #t
create table tblA(noid int, fQty int)
insert into tblA select 1, 2000
union all select 2, 90
union all select 3, 200
union all select 5, 500
union all select 6, 10
union all select 8, 20
create table #t(no int)
declare @min int ,@max int
select @min=min(noid) from tbla
select @max=max(noid) from tbla
While(@min<=@max)
begin
insert into #t values(@min)
set @min=@min+1
endselect * from #t where no not in(select noid from tbla)drop table #t
create table tblA(noid int,fQty int)
goinsert into tblA
select 1,2000
union all select 2,90
union all select 3,200
union all select 5,500
union all select 6,10
union all select 8,20
declare @maxid int
select @maxid=max(noid) from tblAset rowcount @maxidselect @maxid
select id=identity(bigint,1,1) into #t from sysobjects a,sysobjects b,sysobjects c
select id from #t a left join tblA b on a.id=b.noid
where b.noid is nulldrop table #t
--结果:
id
--------------------
4
7(所影响的行数为 2 行)
DECLARE @t TABLE(ID int IDENTITY,b bit)
INSERT @t(b) SELECT TOP 8000 0 FROM syscolumns a,syscolumns b
select c.id from
(select a.ID,b.noid from @t left outer join tblA b on a.ID=b.noid)
c where c.noid is null
的不错
1 张山
3 历史
4 王无
6 李强
桥三
大兵我想对没有编号的进行编号的自动处理,但是已经有编号的不要进行修改。
如果发现有断号的就将号码分配给没有编号的。
结果如下:
编号 姓名
1 张山
3 历史
4 王无
6 李强
2 桥三
5 大兵
遍历记录,如果id为空,则找出最小未使用id,然后update找最小未使用id如下:
select (case when exists (select 1 from test where id=1)
then min(id+1) else 1 end)
from test
where id not in(select id-1 from test)
现在数据库由于需要要自己定义唯一编号,唯一编号由3个数字组成。这三个数字我存储为nvarchar.
由于唯一编号在使用中不可能超过999条记录,所以3个足够用。但是碰到一个问题.第一次使用时添加记录可以001,002,003...这样顺序添加.
当对这些记录进行删除之后再次添加.要核对输入的记录是否重复。这样非常被动。
每次添加记录都要验证该编号是否存在。设计这样的数据库,实在是没辙.
现在有没有办法能够自己得出一个在999内的未使用的编号呢。
非常感谢。用存储过程亦可。我有想过遍历,但是效率不高。希望有更简洁的方式。--1、没有数据的情况
if object_id('pubs..t') is not null
drop table t
gocreate table t(id char(3))select (case when exists (select 1 from t where cast(id as int)=1)
then right('00' + rtrim(cast(min(id + 1) as char(3))) , 3) else '001' end)
as 最小数据
from t
where id not in(select id - 1 from t)drop table t--结果
最小数据
-----
001(所影响的行数为 1 行)
--2、有数据且连号。
if object_id('pubs..t') is not null
drop table t
gocreate table t(id char(3))insert into t(id) values('001')
insert into t(id) values('002')
insert into t(id) values('003')select (case when exists (select 1 from t where cast(id as int)=1)
then right('00' + rtrim(cast(min(id + 1) as char(3))) , 3) else '001' end)
as 最小数据
from t
where id not in(select id - 1 from t)drop table t--结果
最小数据
-----
004(所影响的行数为 1 行)
--3、有数据但断号
if object_id('pubs..t') is not null
drop table t
gocreate table t(id char(3))insert into t(id) values('001')
insert into t(id) values('002')
insert into t(id) values('004')select (case when exists (select 1 from t where cast(id as int)=1)
then right('00' + rtrim(cast(min(id + 1) as char(3))) , 3) else '001' end)
as 最小数据
from t
where id not in(select id - 1 from t)drop table t--结果
最小数据
-----
003(所影响的行数为 1 行)
----------- -----------
2 2
4 5
////////declare @T table (id int)
insert into @T values(1)
insert into @T values(3)
insert into @T values(6)
insert into @T values(7)
select id+1 as id1,id+s-1 as id2 from
(
select a.id as id, min(b.id-a.id) as S from @T a , @T b where a.id<b.id-1
group by a.id
) as t
declare @T table (id int)
insert into @T values(1)
insert into @T values(3)
insert into @T values(6)
insert into @T values(7)
insert into @T values(8)
insert into @T values(9)
insert into @T values(11)
insert into @T values(12)
insert into @T values(13)select id+1 as id1,id+s-1 as id2 from
(
select a.id as id, min(b.id-a.id) as S from @T a , @T b where a.id<b.id
group by a.id
) as t where s>1
union all
select 2 , 90
union all
select 3 , 200
union all
select 5 , 500
union all
select 6 , 10
union all
select 8 , 20
select a.noid+1
from
(
select noid from tb
union all
select 0)a,(select max(noid) maxnoid from tb) b
where not exists(select 1 from tb where noid=a.noid+1) and a.noid<b.maxnoid
select a.noid+1 as noid
from 单据表 a
left join 单据表 b
on b.noid=a.noid+1
where b.noid is null
真牛