sql server给反复列加序列号

2012-11-10

sql server给重复列加序列号CREATE TABLE [dbo].[test1]([id] [bigint] IDENTITY(1,1) NOT NULL,[Name] [n

sql server给重复列加序列号
CREATE TABLE [dbo].[test1]
(
[id] [bigint] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL
)
insert into test1(A)
insert into test1(A)
insert into test1(B)
insert into test1(C)
insert into test1(C)
insert into test1(C)
insert into test1(D)
insert into test1(A)
insert into test1(D)
insert into test1(B)

SELECT * FROM TEST1
id name
1 A
2 A
3 B
4 C
5 C
6 C
7 D
8 A
9 D
10 B

我想更新表得到如结果
1 A
2 A1
3 B
4 C
5 C1
6 C2
7 D
8 A2
9 D1
10 B1

求帮助

[解决办法]

SQL code

UPDATE  test1SET     NAME = NAME + CASE WHEN CONVERT(VARCHAR, N.no) ='0' THEN '' ELSE CONVERT(VARCHAR, N.no) ENDFROM    test1        INNER JOIN ( SELECT id ,                            ROW_NUMBER() OVER ( PARTITION BY NAME ORDER BY GETDATE() )                            - 1 AS no                     FROM   test1                   ) N ON test1.id = N.ID                   SELECT * FROM test1/*id    Name1    A2    A13    B14    C5    C16    C27    D8    A29    D110    B*/
[解决办法]
declare @num int
update a set Name=name+case when @num=0 then '' else rtrim(@num) end,
      @num=(select count(*) from test1 where name=a.name and id<a.id)
from test1 a

热点排行

SQL Server

sql server给反复列加序列号