SQL 批量重新排序行

Question

我有一个包含记录列表的表和一个名为“顺序”的列。我有一个 AJAX 脚本来向上或向下拖放表行，我想用它来执行查询，并在拖动行时对行进行重新排序。

在 PHP 中，我执行查询来获取记录的当前顺序。例如 1, 2, 3 ,4 AJAX 函数在拖/放完成后传递新顺序，例如 3、1、2、4

是否有一种简单的方法可以根据新值一次性对记录重新排序？我能看到的唯一其他选择是循环更新语句，例如 SET order = 1 where order = 3

但这肯定会导致 2 条记录具有相同的值吗？

抱歉，我知道这个描述可能有点令人困惑。

Answer 1

订单不应该是你的主键；使用 where 子句中的主键进行更新。

如果您确实愿意，可以使用相当长的 CASE 语句在一个查询中完成所有操作。示例：（

UPDATE foo
  SET order = CASE order
    WHEN 1 THEN 2
    WHEN 2 THEN 3
    WHEN 3 THEN 4
    WHEN 4 THEN 5
  END
WHERE order IN (1,2,3,4)

请记住，SQL 语句的行为就好像它们同时更改所有值，因此不会执行将 1 更改为 2，然后更改为 3 等操作。）

Answer 2

如果您知道新的行位置，您可以执行以下操作：

CREATE PROCEDURE [proc_UpdateCountryRowOrder]
    @ID UNIQUEIDENTIFIER,
    @NewPosition INT
AS

SET NOCOUNT ON

DECLARE @CurrentPosition INT
DECLARE @MaximumPosition INT

IF (@NewPosition < 1) SET @NewPosition = 1

SELECT @CurrentPosition = [Countries].[Order]
FROM [Countries]
WHERE [Countries].[ID] = @ID

SELECT @MaximumPosition = MAX([Countries].[Order])
FROM [Countries]

IF (@NewPosition > @MaximumPosition) SET @NewPosition = @MaximumPosition

IF (@NewPosition <> @CurrentPosition)
BEGIN
    IF (@NewPosition < @CurrentPosition)
    BEGIN
        BEGIN TRAN

        UPDATE [Countries]
        SET [Countries].[Order] = [Countries].[Order] + 1
        WHERE [Countries].[Order] >= @NewPosition
        AND [Countries].[Order] < @CurrentPosition

        UPDATE [Countries]
        SET [Countries].[Order] = @NewPosition
        WHERE ID = @ID

        COMMIT TRAN
    END
    ELSE
    BEGIN
        BEGIN TRAN

        UPDATE [Countries]
        SET [Countries].[Order] = [Countries].[Order] - 1
        WHERE [Countries].[Order] <= @NewPosition
        AND [Countries].[Order] > @CurrentPosition

        UPDATE [Countries]
        SET [Countries].[Order] = @NewPosition
        WHERE ID = @ID

        COMMIT TRAN
    END
END
GO

Answer 3

您确实想回去看看 ajax 函数的结果。看看是否可以获得重新排序的元素列表，而不仅仅是新的列表顺序。

您可以编写一个函数（在 C#/VB 中）来在给定之前和之后列表的代码中确定这一点。一旦发生增量更改，您就可以针对每次重新订购发出一个更新命令来发出更新事务。

如果您只想使用新订单。尝试添加一个名为 neworder 的新列并更新它。然后在循环完成后批量更新 currentcolumn=newcolumn 。

Answer 4

你可以做类似的事情

BEGIN
UPDATE Table SET order = order + 1 WHERE order >= new_pos
UPDATE Table SET order = new_pos WHERE order = old_pos
UPDATE Table SET order = order - 1 WHERE order < old_pos AND order > new_pos
COMMIT;

_{（未经测试，这只是一个指针）}