向上或向下移动记录时，SQL Server重新排序序列号

Question

我在SQL Server中有一个表，其中包含Id, Plan_Id, sequence_no和以下数据。

Id          Plan_Id      sequence_no
----------- ------------ -----------
1132507748  1167096719   0
1102670655  1167096719   1
1166210290  1167096719   2

因此，我在网站上列出了基于sequence_no的这些记录。用户可以使用拖拽对这些记录进行排序；滴

我想要的是，当用户将第1条记录拖动到第0个位置时，我想重新排序表中的所有记录。我有Id、Plan_Id和new sequence_no作为过程的参数。我尝试了下面的代码，但它不起作用。

update dbo.planRecords
set sequence_no = sequence_no+1
where plan_id = @plan_id and sequence_no >= @newPosition

如何重新排序这些记录？

Answer 1

在移动项目之前，您需要知道旧位置。根据项目是向上还是向下移动，您的逻辑需要有所不同。该过程的大致轮廓(未测试(如下：

DECLARE @Id INT = 1100000004; -- this id
DECLARE @NewPosition INT = 1; -- needs to have this position
WITH RowToMove AS (
-- using cte instead of variables
SELECT Plan_Id, sequence_no AS OldPosition
FROM planRecords
WHERE Id = @Id
), RowsToUpdate AS (
-- columns used inside set and where clause of the update statement
SELECT Id, sequence_no, OldPosition
FROM planRecords
CROSS JOIN RowToMove
-- select rows that belong to same category and position between old and new
WHERE planRecords.Plan_Id = RowToMove.Plan_Id AND sequence_no BETWEEN 
CASE WHEN OldPosition < @NewPosition THEN OldPosition ELSE @NewPosition END AND
CASE WHEN OldPosition > @NewPosition THEN OldPosition ELSE @NewPosition END
)
UPDATE RowsToUpdate SET sequence_no = CASE
WHEN Id = @Id THEN @NewPosition -- this is the row we are moving
WHEN OldPosition < @NewPosition THEN sequence_no - 1 -- row was moved down, move other rows up
WHEN OldPosition > @NewPosition THEN sequence_no + 1 -- row was moved up, move other rows down
END;

使用变量在DBFiddle上演示，使用CTE

Answer 2

最简单的解决方案是不使用int，而是使用decimal。这样，您只需要知道记录中最接近的两个兄弟，并且只需要将sequence_no的值更改为它们之间的一个数字。

例如，如果您的数据库中有以下记录：

Id          Plan_Id      sequance_no
----------- ------------ -----------
1132507748  1167096719   1.0
1102670655  1167096719   2.0
1166210290  1167096719   3.0
1132507763  1167096719   4.0
1102670623  1167096719   5.0
1166210299  1167096719   6.0

如果您想将记录5.0移动到1.0和2.0之间，您所需要做的就是将其sequence_no更新为1.5:

Id          Plan_Id      sequance_no
----------- ------------ -----------
1132507748  1167096719   1.0
1102670623  1167096719   1.5
1102670655  1167096719   2.0
1166210290  1167096719   3.0
1132507763  1167096719   4.0
1166210299  1167096719   6.0

Decimal最多支持38位数字，因此您可以使用小数点右侧的37位数字，所以即使您对记录的顺序有很多更改，我相信您也不必担心插槽用完。

如果您想以整数显示记录的顺序，您可以始终使用row_number() over(order by sequence_no)来获得良好的int值显示。