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求帮助
(
[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求帮助
(
[id] [bigint] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL
)
insert into test1 values('A')
insert into test1 values('A')
insert into test1 values('B')
insert into test1 values('C')
insert into test1 values('C')
insert into test1 values('C')
insert into test1 values('D')
insert into test1 values('A')
insert into test1 values('D')
insert into test1 values('B')select id,Name=Name+case when no=1 then '' else rtrim(no-1) end from
(select *,no=row_number() over(partition by Name order by id) from test1) t
order by id/*
id Name
-------------------- ----
1 A
2 A1
3 B
4 C
5 C1
6 C2
7 D
8 A2
9 D1
10 B1(10 row(s) affected)
SET NAME = NAME + CASE WHEN CONVERT(VARCHAR, N.no) ='0' THEN '' ELSE CONVERT(VARCHAR, N.no) END
FROM 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 Name
1 A
2 A1
3 B1
4 C
5 C1
6 C2
7 D
8 A2
9 D1
10 B*/
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
id name
1 A
2 A
3 B
4 C
5 C
6 C
7 D
8 A1
9 D
10 B怎么办?
id name
1 A
2 A1
3 B1
4 C
5 C1
6 C2
7 D
8 A1
9 D1
10 B
有两个重复的A1 我希望一个变成A2