字段: zbh
LZ001
LZ002
LZ002-1
LZ003
LZ003-1
LZ004
LZ005
....
怎么样一次更新成
DB001
DB002
DB003
DB004
DB005
DB006
DB007 ...把left(zbh,2)='LZ'编码按下面的顺序排列出来!! 给怎么写,,大家帮帮忙!!!
LZ001
LZ002
LZ002-1
LZ003
LZ003-1
LZ004
LZ005
....
怎么样一次更新成
DB001
DB002
DB003
DB004
DB005
DB006
DB007 ...把left(zbh,2)='LZ'编码按下面的顺序排列出来!! 给怎么写,,大家帮帮忙!!!
declare @i int
set @i=1000
update T
set Col='DB'+right(@i,3),@i=@i+1
set COl='DB'+(select right('000'+rtrim(count(1)),3) from T where col<=a.COl)
from
t a
where
left(Col,2)='LZ'
insert into @tb select 'LZ001'
insert into @tb select 'LZ002'
insert into @tb select 'LZ002-1'insert into @tb select 'LZ003'
insert into @tb select 'LZ003-1'
insert into @tb select 'LZ004'
insert into @tb select 'LZ005'update t1 set id=t2.orderid from @tb t1,
(select 'DB'+right('000'+cast(row_number() over (order by id) as varchar(3)),3) as orderid,* from @tb )t2
where t1.id=t2.idselect * from @tbDB001
DB002
DB003
DB004
DB005
DB006
DB007
insert into @tb select 'LZ001'
insert into @tb select 'LZ002'
insert into @tb select 'LZ002-1'insert into @tb select 'LZ003'
insert into @tb select 'LZ003-1'
insert into @tb select 'LZ004'
insert into @tb select 'LZ005'update a
set id='DB'+(select right('000'+rtrim(count(1)),3) from @tb where id<=a.id)
from @tb aselect * from @tbDB001
DB002
DB003
DB004
DB005
DB006
DB007
MB002
MB002
MB004
MB004
MB006
MB006
MB008
MB008
MB010
MB010
declare @tb table (id varchar(50))
insert into @tb select 'LZ001'
insert into @tb select 'LZ002'
insert into @tb select 'LZ002-1'insert into @tb select 'LZ003'
insert into @tb select 'LZ003-1'
insert into @tb select 'LZ004'
insert into @tb select 'LZ005'--修改
declare @i int
set @i=0
update @tb set id='DB'+right('000'+ltrim(@i),3),@i=@i+1--查看
select * from @tb