UID dmz dmyy
12242 010100 军事代表
12249 010100 经理
12250 010200 副经理
12371 010200 革委会副主任
13701 010300 其它领导
12273 010400 政治部副主任
12309 010400 党委副书记 先查询到表中重复的记录,再把记录重复的序号加一位使之变成不重复的序号,用sql语句实现
例如:020000 相应变成 0200001,0200002..... declare @i int
declare @sql varchar(500)
set @i=0
begin
set @sql='update InternalUnits set dmz=a.dmz+'
set @i=@i+1
set @sql=@sql+''''+cast(@i as varchar)+''' from (select * from InternalUnits where dmz
in(select dmz from InternalUnits group by dmz having count(dmz)>1)) a where a.dmz=InternalUnits.dmz'
--print @sql
exec (@sql)
end这个@i没有实现自增长,请问怎么才能实现自增长呢?
12242 010100 军事代表
12249 010100 经理
12250 010200 副经理
12371 010200 革委会副主任
13701 010300 其它领导
12273 010400 政治部副主任
12309 010400 党委副书记 先查询到表中重复的记录,再把记录重复的序号加一位使之变成不重复的序号,用sql语句实现
例如:020000 相应变成 0200001,0200002..... declare @i int
declare @sql varchar(500)
set @i=0
begin
set @sql='update InternalUnits set dmz=a.dmz+'
set @i=@i+1
set @sql=@sql+''''+cast(@i as varchar)+''' from (select * from InternalUnits where dmz
in(select dmz from InternalUnits group by dmz having count(dmz)>1)) a where a.dmz=InternalUnits.dmz'
--print @sql
exec (@sql)
end这个@i没有实现自增长,请问怎么才能实现自增长呢?
(
id int,
dmz varchar(10),
dmyy varchar(30)
)
insert into t1
select 12242, '010100', '军事代表' union all
select 12249, '010100', '经理' union all
select 12250, '010200', '副经理' union all
select 12371, '010200', '革委会副主任' union all
select 13701, '010300', '其它领导' union all
select 12273, '010400', '政治部副主任' union all
select 12309, '010400', '党委副书记';with aaa as
(select ROW_NUMBER() over(partition by dmz order by id) as row,* from t1)
select id,dmz+CAST(row as varchar) as dmz,dmyy from aaa
(select ROW_NUMBER() over(partition by dmz order by id) as row,* from t1)
,bbb as
(select id,dmz+CAST(row as varchar) as dmz,dmyy from aaa)
update a
set dmz=b.dmz from t1 as a inner join bbb as b on a.id=b.id