求插入算法问题？

[particular]

表中有一个字段格式如下：
field
001
002
005
006
007
010
现在想往其中插入数据占003的位置，之后在插入数据占004的位置，在之后为008,009，不知如何写，另处如果有100条数据前98条顺序号都是接着的，插入第099行，如果顺次计算，算法会不会很笨。

[victorycyz]

为什么会有这种需求，你清空数据或建新表，然后插入1到100的值不就行了。

[gmlxf]

取出第一缺位：
select min(field)+1 from t a where exists(select 1 from t where field<>field+1)

[zqfleaf]

select top 1 a.number from
(select IDENTITY(INT,1,1) as Number , field from table1 order by field) a
where a.number <> cast(a.field as integer)
order by a.number

[gmlxf]

drop table #t
create table #t(field varchar(10),uname varchar(10))
insert #t
select '001','a'
union all select '002','b'
union all select '005','c'
union all select '006','d'
union all select '007','e'
union all select '010','f'
select * from #t

--select right(('000'+cast((min(field)+1) as varchar)),3) from #t a where not exists(select 1 from #t where cast(field as int)=cast(a.field as int)+1)
insert #t
select (select right(('000'+cast((min(field)+1) as varchar)),3) from #t a where not exists(select 1 from #t where cast(field as int)=cast(a.field as int)+1)),'gg'

select * from #t order by field

[orcale]

楼主理解错误了吧
select field from table order by field asc
不就可以了

[gmlxf]

--test：
create table #t(field varchar(10),uname varchar(10))
insert #t
select '001','a'
union all select '002','b'
union all select '005','c'
union all select '006','d'
union all select '007','e'
union all select '010','f'
select * from #t

--select right(('000'+cast((min(field)+1) as varchar)),3) from #t a where not exists(select 1 from #t where cast(field as int)=cast(a.field as int)+1)
insert #t
select (select right(('000'+cast((min(field)+1) as varchar)),3) from #t a where not exists(select 1 from #t where cast(field as int)=cast(a.field as int)+1)),'gg'

select * from #t order by field

drop table #t

[gmlxf]

field uname
---------- ----------
001 a
002 b
005 c
006 d
007 e
010 f

（所影响的行数为 6 行）


（所影响的行数为 1 行）

field uname
---------- ----------
001 a
002 b
003 gg --新增加的行
005 c
006 d
007 e
010 f

（所影响的行数为 7 行）

[particular]

select right(('000'+cast((min(field)+1) as varchar)),3) from #t a where not exists(select 1 from #t where cast(field as int)=cast(a.field as int)+1)

这句没看明白能解释一下吗？

[wzh1215]

select right(('000'+cast((min(field)+1) as varchar)),3) from #t a where not exists(select 1 from #t where cast(field as int)=cast(a.field as int)+1)
这是表的自链接，right(('000'+cast((min(field)+1) as varchar)),3)是转换数值型为字符型。
cast(field as int)=cast(a.field as int)+1)表内field是否有连续的下一个数据！