sql-server - 永久移动和重新排序记录

Question

我有一个表Items例如

ID  Name     ItemOrder
----------------------
16  Albania  1
13  Chile    2
11  Barbados 3 
10  France   4
...

我在客户端应用程序中显示为

SELECT * FROM Items ORDER BY ItemOrder

我希望能够“移动”，例如阿尔巴尼亚和巴巴多斯之后(或之前，取决于我的参数)法国或任何其他记录。所以结果是(对于移动After记录France):

13  Chile    1    
10  France   2 
16  Albania  3 
11  Barbados 4 

Before 的结果是:

13  Chile    1        
16  Albania  2 
11  Barbados 3 
10  France   4 

我需要永久更新ItemOrder以反射(reflect)新订单。有没有一种简单有效的方法来做到这一点？

Answer 1

您可以使用公用表表达式 (CTE) 来执行 UPDATE 操作。对于“After”情况，您的查询可能如下所示:

;WITH ToUpdate AS (
  SELECT ID, Name, ItemOrder,
         ROW_NUMBER() OVER (ORDER BY temp_order) AS NewOrder
  FROM (
  SELECT ID, Name, ItemOrder,
         CASE 
            WHEN Name IN ('Albania', 'Barbados') THEN x.o + ItemOrder * 0.1 
          ELSE ItemOrder
         END AS temp_order
  FROM mytable 
  CROSS JOIN (SELECT ItemOrder FROM mytable WHERE Name = 'France') AS x(o)) AS t
)
UPDATE ToUpdate
SET ItemOrder = NewOrder

诀窍是获取法国订单号并添加“阿尔巴尼亚”、“巴巴多斯”订单的分数，这样:

• “阿尔巴尼亚”、“巴巴多斯”位于“法国”之后
• “阿尔巴尼亚”和“巴巴多斯”维持着它们之间的秩序
• “法国”之后的记录顺序也保持不变。

Demo here

编辑:

如果我们替换这一行，就可以实现“之前”的情况:

WHEN Name IN ('Albania', 'Barbados') THEN x.o + ItemOrder * 0.1 

用这一行:

WHEN Name IN ('Albania', 'Barbados') THEN x.o - 1.0/ItemOrder * 0.1