27,579
社区成员
发帖
与我相关
我的任务
分享
-- 再执行一下 update 语句
create table test(ID int , NAME varchar(10) , Loginname varchar(20))
go
insert into test values
(1,'白洁','baijie'),
(2,'白杰','baijie'),
(3,'王静','wangjing'),
(4,'王京','wangjing'),
(5,'王井','wangjing'),
(6,'王经','wangjing')
go
with m as (
select * , row_number() over(partition by loginname order by id) rn
from test
)
update m set Loginname = Loginname + cast(rn - 1 as varchar(2))
where rn > 1
go
select * from test
go
drop table test
go
(6 行受影响)
(4 行受影响)
ID NAME Loginname
----------- ---------- --------------------
1 白洁 baijie
2 白杰 baijie1
3 王静 wangjing
4 王京 wangjing1
5 王井 wangjing2
6 王经 wangjing3
(6 行受影响)
WITH t1 AS (
SELECT ID,
name,
dbo.Fn_GetPinYin(name) PinYin
FROM [USER]
)
,t2 AS (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY PinYin
ORDER BY name,id
)-1 AS rn
FROM t1
)
UPDATE [USER]
SET [USER].Loginname = t2.PinYin + (CASE WHEN rn=0 THEN
''
ELSE
CONVERT(varchar(11),rn)
END)
FROM [USER], t2
WHERE [USER].ID = t.ID