sql-server - 更新表中的顺序列但保持顺序

Question

我有一张 table

id | title | F_ID  | order
----------------------------
1  | test 1| 1     | 44
2  | test 3| 1     | 3
3  | test 4| 1     | 1
4  | test 5| 2     | 1

我想将 F_ID 为 1 但保持顺序的所有行的顺序列更新为 +10

结果需要是

id | title | F_ID  | order
----------------------------
1  | test 1| 1     | 30
2  | test 3| 1     | 20
3  | test 4| 1     | 10
4  | test 5| 2     | 1

我可以插入所有我想更新的行到临时表然后循环行并通过 [id] 更新真实表中的每一行。

也许有更好的选择？

Answer 1

我认为这应该可行:

SQL Fiddle

MS SQL Server 2008 架构设置:

create table test (id int, title  varchar(49), F_ID int, [order] int)
insert test values 
(1  , 'test 1', 1, 44),
(2  , 'test 3', 1, 3),
(3  , 'test 4', 1, 1),
(4  , 'test 5', 2, 1)

查询 1:

update test
set [order] = new_order
from test t
inner join (
    select 
       id, 
       new_order = ROW_NUMBER() over (partition by f_id order by [order]) * 10
    from test t
    where f_id = 1
) t2 
on t.id = t2.id

Results :

查询 2:

select * from test

Results :

| ID |  TITLE | F_ID | ORDER |
|----|--------|------|-------|
|  1 | test 1 |    1 |    30 |
|  2 | test 3 |    1 |    20 |
|  3 | test 4 |    1 |    10 |
|  4 | test 5 |    2 |     1 |