我想在程序中实现编号自动生成，在数据中间插入的时候，后面的数据编号自动改变
我想在程序中实现编号自动生成，在数据中间插入的时候，后面的数据编号自动改变
例如1.1，后面是1.11，1.12，我在中间插入1.11，后面的1.11，1.12自动变成1.12，1.13。
如果1.1后面是1.2，1.3，那就自动变成1.3，1.4。
谁有办法？
原数据：
1
2
2.1
2.1.1
2.2
3
3.1
插入 2
变成
原 现
1 1
插入 2
2 3
2.1 3.1
2.1.1 3.1.1
2.2 3.2
3 4
3.1 4.1
对否？
create function dbo.addid(@addid varchar(30),@id varchar(30))
returns varchar(30)
as
begin
declare @newid varchar(30),@mid int
--@addid = 1.2.1 @id = 1.2.2.1
if @addid >= @id return @id
-- [1.2.1 => 1.2.] @addid去尾
if charindex('.',@addid)>0 set @addid = left(@addid,len(@addid)+1-charindex('.',reverse(@addid)))
--[1.2.2.1 =>2.1 @newid = 1.2.]@id去头，并将头存入@newid
select @newid = @addid+'.',@id = stuff(@id,1,len(@addid)+1,'')
--@id去中间部分,并存入@mid [2.1=>.1 @mid = 2]
if charindex('.',@id)> 1
begin
set @mid = left(@id,charindex('.',@id)-1)
set @id = stuff(@id,1,charindex('.',@id)-1)
end
else
begin
set @mid = @id
set @id = ''
end
--[@newid = 1.2. + 3 + .1 中间部门加1合并
set @newid = @newid+rtrim(@mid+1)+@id
return @newid
end
go
--例子，可以将UPDATE 语句放入表的INSERT 触发器。
declare @id varchar(30)
insert tb(id) select @id
update tb set id = dbo.addid(@id,id) where id > @id
create table tb(id varchar(30))
insert tb select '1'
union all select '1.1'
union all select '2'
union all select '2.1'
union all select '2.1.2'
union all select '2.2'
union all select '3'
union all select '3.1'
go
create function dbo.addid(@addid varchar(30),@id varchar(30))
returns varchar(30)
as
begin
declare @newid varchar(30),@mid int
--@addid = 1.2.1 @id = 1.2.2.1
if @addid > @id return @id
-- [1.2.1 => 1.2.] @addid去尾
if charindex('.',@addid)>0 set @addid = left(@addid,len(@addid)+1-charindex('.',reverse(@addid)))
--[1.2.2.1 =>2.1 @newid = 1.2.]@id去头，并将头存入@newid
--前缀不同的直接返回
if @addid <> left(@id,len(@addid)) return @id
select @newid = @addid,@id = stuff(@id,1,len(@addid),'')
--@id去中间部分,并存入@mid [2.1=>.1 @mid = 2]
if charindex('.',@id)> 1
begin
set @mid = left(@id,charindex('.',@id)-1)
set @id = stuff(@id,1,charindex('.',@id)-1,'')
end
else
begin
set @mid = @id
set @id = ''
end
--[@newid = 1.2. + 3 + .1 中间部门加1合并
set @newid = @newid+rtrim(@mid+1)+@id
return @newid
end
go
create trigger t_tb on tb instead of insert
as
begin
--只支持单行插入，如果多行对inserted表使用游标
update a set id = dbo.addid(b.id,a.id) from tb a,inserted b where a.id >= b.id
insert tb select * from inserted
end
go
select * from tb order by id
/*
id
------------------------------
1
1.1
2
2.1
2.1.2
2.2
3
3.1
*/
insert tb(id) select '2.1'
select * from tb order by id
/*
id
------------------------------
1
1.1
2
2.1
2.2
2.2.2
2.3
3
3.1
*/
go
drop table tb
drop function dbo.addid
create table tb(id varchar(30))
insert tb select '1'
union all select '1.1'
union all select '2'
union all select '2.1'
union all select '2.1.2'
union all select '2.2'
union all select '3'
union all select '3.1'
go
create function dbo.addid(@addid varchar(30),@id varchar(30))
returns varchar(30)
as
begin
declare @newid varchar(30),@mid int
--@addid = 1.2.1 @id = 1.2.2.1
if @addid > @id return @id
-- [1.2.1 => 1.2.] @addid去尾
if charindex('.',@addid)>0
set @addid = left(@addid,len(@addid)+1-charindex('.',reverse(@addid)))
else set @addid = ''
--[1.2.2.1 =>2.1 @newid = 1.2.]@id去头，并将头存入@newid
--前缀不同的直接返回
if @addid <> left(@id,len(@addid)) return @id
select @newid = @addid,@id = stuff(@id,1,len(@addid),'')
--@id去中间部分,并存入@mid [2.1=>.1 @mid = 2]
if charindex('.',@id)> 1
begin
set @mid = left(@id,charindex('.',@id)-1)
set @id = stuff(@id,1,charindex('.',@id)-1,'')
end
else
begin
set @mid = @id
set @id = ''
end
--[@newid = 1.2. + 3 + .1 中间部门加1合并
set @newid = @newid+rtrim(@mid+1)+@id
return @newid
end
go
create trigger t_tb on tb instead of insert
as
begin
--只支持单行插入，如果多行对inserted表使用游标
update a set id = dbo.addid(b.id,a.id) from tb a,inserted b where a.id >= b.id
insert tb select * from inserted
end
go
select * from tb order by id
/*
id
------------------------------
1
1.1
2
2.1
2.1.2
2.2
3
3.1
*/
insert tb(id) select '2.1'
select * from tb order by id
/*
id
------------------------------
1
1.1
2
2.1
2.2
2.2.2
2.3
3
3.1
*/
insert tb(id) select '2'
select * from tb order by id
/*
id
------------------------------
1
1.1
2
3
3.1
3.2
3.2.2
3.3
4
4.1
*/
go
drop table tb
drop function dbo.addid
例如1.1，后面是1.11，1.12，我在中间插入1.11，后面的1.11，1.12自动变成1.12，1.13。
如果1.1后面是1.2，1.3，那就自动变成1.3，1.4。
谁有办法？
原数据：
1
2
2.1
2.1.1
2.2
3
3.1
插入 2
变成
原 现
1 1
插入 2
2 3
2.1 3.1
2.1.1 3.1.1
2.2 3.2
3 4
3.1 4.1
对否？
create function dbo.addid(@addid varchar(30),@id varchar(30))
returns varchar(30)
as
begin
declare @newid varchar(30),@mid int
--@addid = 1.2.1 @id = 1.2.2.1
if @addid >= @id return @id
-- [1.2.1 => 1.2.] @addid去尾
if charindex('.',@addid)>0 set @addid = left(@addid,len(@addid)+1-charindex('.',reverse(@addid)))
--[1.2.2.1 =>2.1 @newid = 1.2.]@id去头，并将头存入@newid
select @newid = @addid+'.',@id = stuff(@id,1,len(@addid)+1,'')
--@id去中间部分,并存入@mid [2.1=>.1 @mid = 2]
if charindex('.',@id)> 1
begin
set @mid = left(@id,charindex('.',@id)-1)
set @id = stuff(@id,1,charindex('.',@id)-1)
end
else
begin
set @mid = @id
set @id = ''
end
--[@newid = 1.2. + 3 + .1 中间部门加1合并
set @newid = @newid+rtrim(@mid+1)+@id
return @newid
end
go
--例子，可以将UPDATE 语句放入表的INSERT 触发器。
declare @id varchar(30)
insert tb(id) select @id
update tb set id = dbo.addid(@id,id) where id > @id
create table tb(id varchar(30))
insert tb select '1'
union all select '1.1'
union all select '2'
union all select '2.1'
union all select '2.1.2'
union all select '2.2'
union all select '3'
union all select '3.1'
go
create function dbo.addid(@addid varchar(30),@id varchar(30))
returns varchar(30)
as
begin
declare @newid varchar(30),@mid int
--@addid = 1.2.1 @id = 1.2.2.1
if @addid > @id return @id
-- [1.2.1 => 1.2.] @addid去尾
if charindex('.',@addid)>0 set @addid = left(@addid,len(@addid)+1-charindex('.',reverse(@addid)))
--[1.2.2.1 =>2.1 @newid = 1.2.]@id去头，并将头存入@newid
--前缀不同的直接返回
if @addid <> left(@id,len(@addid)) return @id
select @newid = @addid,@id = stuff(@id,1,len(@addid),'')
--@id去中间部分,并存入@mid [2.1=>.1 @mid = 2]
if charindex('.',@id)> 1
begin
set @mid = left(@id,charindex('.',@id)-1)
set @id = stuff(@id,1,charindex('.',@id)-1,'')
end
else
begin
set @mid = @id
set @id = ''
end
--[@newid = 1.2. + 3 + .1 中间部门加1合并
set @newid = @newid+rtrim(@mid+1)+@id
return @newid
end
go
create trigger t_tb on tb instead of insert
as
begin
--只支持单行插入，如果多行对inserted表使用游标
update a set id = dbo.addid(b.id,a.id) from tb a,inserted b where a.id >= b.id
insert tb select * from inserted
end
go
select * from tb order by id
/*
id
------------------------------
1
1.1
2
2.1
2.1.2
2.2
3
3.1
*/
insert tb(id) select '2.1'
select * from tb order by id
/*
id
------------------------------
1
1.1
2
2.1
2.2
2.2.2
2.3
3
3.1
*/
go
drop table tb
drop function dbo.addid
create table tb(id varchar(30))
insert tb select '1'
union all select '1.1'
union all select '2'
union all select '2.1'
union all select '2.1.2'
union all select '2.2'
union all select '3'
union all select '3.1'
go
create function dbo.addid(@addid varchar(30),@id varchar(30))
returns varchar(30)
as
begin
declare @newid varchar(30),@mid int
--@addid = 1.2.1 @id = 1.2.2.1
if @addid > @id return @id
-- [1.2.1 => 1.2.] @addid去尾
if charindex('.',@addid)>0
set @addid = left(@addid,len(@addid)+1-charindex('.',reverse(@addid)))
else set @addid = ''
--[1.2.2.1 =>2.1 @newid = 1.2.]@id去头，并将头存入@newid
--前缀不同的直接返回
if @addid <> left(@id,len(@addid)) return @id
select @newid = @addid,@id = stuff(@id,1,len(@addid),'')
--@id去中间部分,并存入@mid [2.1=>.1 @mid = 2]
if charindex('.',@id)> 1
begin
set @mid = left(@id,charindex('.',@id)-1)
set @id = stuff(@id,1,charindex('.',@id)-1,'')
end
else
begin
set @mid = @id
set @id = ''
end
--[@newid = 1.2. + 3 + .1 中间部门加1合并
set @newid = @newid+rtrim(@mid+1)+@id
return @newid
end
go
create trigger t_tb on tb instead of insert
as
begin
--只支持单行插入，如果多行对inserted表使用游标
update a set id = dbo.addid(b.id,a.id) from tb a,inserted b where a.id >= b.id
insert tb select * from inserted
end
go
select * from tb order by id
/*
id
------------------------------
1
1.1
2
2.1
2.1.2
2.2
3
3.1
*/
insert tb(id) select '2.1'
select * from tb order by id
/*
id
------------------------------
1
1.1
2
2.1
2.2
2.2.2
2.3
3
3.1
*/
insert tb(id) select '2'
select * from tb order by id
/*
id
------------------------------
1
1.1
2
3
3.1
3.2
3.2.2
3.3
4
4.1
*/
go
drop table tb
drop function dbo.addid
