如何在更改行排序后重新排列“排序顺序”列

Question

我有一个表格，有很多行和一列名为“order”，其值从1到XX不等，取决于表格中有多少行。

问题在于，如果我想将“order” 1的行移动到第3个位置，我需要其他行的“order”重新排列。

例如，如果我将“order” 1与“3”交换位置，则后面的所有数字都必须增加1，并且第二个数字现在必须是“order” 1。

希望我表述清楚了。

谢谢您的帮助！

编辑/示例数据：

Name   |  Order
Line 1 |  1
Line 2 |  2
Line 3 |  3
Line 4 |  4

如果我将“Line 1”的顺序从1更新为4，则希望其他行的顺序也得到更新！

Name   |  Order
Line 2 |  1
Line 3 |  2
Line 4 |  3
Line 1 |  4

同样的，如果我将“第4行”的顺序更新为1，则会发生同样的事情！

Name   |  Order
Line 4 |  1
Line 1 |  2
Line 2 |  3
Line 3 |  4

如果我将“第三行”的顺序更新为数字2，情况依然如此！

Name   |  Order
Line 1 |  1
Line 3 |  2
Line 2 |  3
Line 4 |  4

- user8863243

---

这不太清楚，因为我不知道你所说的“移动”一行是什么意思。在 SQL 表中，记录在实际上是无序的。你能否提供一些样本数据来解释你的问题？ - Tim Biegeleisen

听起来有些奇怪，但您考虑过在此“order”列中使用浮点数或其他支持十进制的类型吗？它显然仍然可以定义顺序，但是您可以计算一个新的顺序值（(前一行+后一行)/2），而无需重新编号其他行。（如果您认为有必要，可以定期重新编号所有行，将它们全部带回int值） - Damien_The_Unbeliever

我刚刚更新了我的问题，附带了样本数据和期望的结果 @D-Shih。 - user8863243

我刚刚更新了我的问题，附上了样本数据和期望的结果 @ TimBiegeleisen - user8863243

如果您更新中间行顺序，将会发生什么操作？ - Ahmed Yousif

Answer 1

尝试这种方法

DECLARE @tbl TABLE([Name] VARCHAR(100),[Order] INT);
INSERT INTO @tbl VALUES
 ('Line 1',1) 
,('Line 2',2) 
,('Line 3',3)
,('Line 4',4);

DECLARE @OldPos INT=3, @NewPos INT=2;

WITH cte AS
(
    SELECT [Name]
          ,[Order]
          ,ROW_NUMBER() OVER(ORDER BY CASE WHEN [Order]=@OldPos 
                                           THEN CAST(@NewPos AS FLOAT) 
                                                + CASE WHEN @OldPos<@Newpos 
                                                       THEN 0.5 ELSE -0.5 END 
                                           ELSE CAST([Order] AS FLOAT) END) AS ComputedPos 
    FROM @tbl 
)
UPDATE cte SET [Order] = ComputedPos
WHERE [Order]<>ComputedPos;

SELECT * FROM @tbl ORDER BY [Order];

这里使用了一个可更新的CTE。通过Damian_The_Unbeliever的评论，我想到了这个方法，而无需改变订单数据类型。

ROW_NUMBER函数将在其OVER子句中执行一些计算。如果[Order]拥有正确的输入值，我们将通过添加0.5或-0.5到新位置来设置新位置在新邻居之间。这取决于您移动行的方向。

- Shnugo

---

@SalmanA 确实，谢谢提示。已将 WHERE [Order]<>ComputedPos 添加为最后一行。 - Shnugo

Answer 2

您可以使用一个 CASE 语句：

UPDATE ...
SET [Order] = [Order] + CASE
  WHEN [Order] = @OLDPOS THEN @NEWPOS - @OLDPOS
  WHEN @NEWPOS > @OLDPOS AND [Order] > @OLDPOS AND [Order] <= @NEWPOS THEN -1
  WHEN @NEWPOS < @OLDPOS AND [Order] < @OLDPOS AND [Order] >= @NEWPOS THEN 1
  ELSE 0
END

这里有一些测试

通过在上面添加ROW_NUMBER()可以解决间隙和重叠问题。

更多的一些测试

- Salman A

---

但这也会更新所有行;-) 此外，我们不知道它是否仅是排序顺序，还是OP需要从1到n对行进行编号而没有间隙... - Shnugo

@Shungo 只需添加一个 where 子句 WHERE [Order] BETWEEN smallerof(@Old, @new) AND greaterof(@Old, @new)。但是，是的，它并不能解决间隙问题。 - Salman A

谢谢你的帮助！是的，我总是需要顺序从1到N，没有间隙！！ - user8863243

@MatthieuRigaux 重新考虑后...如果已经存在间隙/重叠，它将无法修复它们，只会将它们移动。使用ROW_NUMBER()方法将是自我纠正的。我可能可以将上述内容转换为ROW_NUMBER()，但已经有一个答案了。 - Salman A

你的解决方案对我很有效，因为我的列表中不会有空隙。我将自动在表格末尾添加新行，并在删除时更新下面的其他行的顺序以填补空缺（如果需要）！非常感谢！ - user8863243