CREATE TABLE [dbo].[TableTest](
[id] [int] primary key,
[name] [varchar](20) DEFAULT('') NOT NULL,
[value] [int] DEFAULT(0) NOT NULL
)
insert TableTest values(1,'A','0')
insert TableTest values(2,'A','0')
insert TableTest values(3,'A','0')
insert TableTest values(4,'B','0')
insert TableTest values(5,'B','0')
insert TableTest values(6,'C','0')
insert TableTest values(7,'D','0')
insert TableTest values(8,'D','0')
insert TableTest values(9,'D','0')
insert TableTest values(10,'D','0')
id name value
----------- -------------------- -----------
1 A 0
2 A 0
3 A 0
4 B 0
5 B 0
6 C 0
7 D 0
8 D 0
9 D 0
10 D 0
-------------------------------------------
开始更新：
WITH TT AS
(SELECT [value],rownum = row_number() over(partition by [name] order by [id]) FROM TableTest )
UPDATE TT SET [value] = rownum
SELECT * FROM TableTest
----------------------------------------------------------------------
(10 行処理されました)
id name value
----------- -------------------- -----------
1 A 1
2 A 2
3 A 3
4 B 1
5 B 2
6 C 1
7 D 1
8 D 2
9 D 3
10 D 4
(10 行処理されました)
