当记录向上或向下移动时，SQL Server 重新排序序列号 [英] SQL Server reorder sequence number when records are moved up or down

问题描述

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

I have one table in SQL Server with Id, Plan_Id, sequence_no and the following data.

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

在网站上，我根据sequence_no列出这些记录.用户可以使用拖拽对这些记录进行排序.下降.

so on web site I am listing these records based on sequence_no. User can sort these records using drag & drop.

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

What I want here is, when user drag 1th record to 0th position then I want to reorder all records in table. I have Id,Plan_id and new sequence_no as parameter to procedure. I tried below code but it won't worked.

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

如何对这些记录重新排序?

How can I reorder these records ?

推荐答案

在移动项目之前，您需要知道旧位置.并且您的逻辑需要根据项目是向上还是向下移动而有所不同.大致流程(未测试)如下:

You need to know the old position before you can move items around. And your logic needs to be different depending on whether the item was moved up or down. Rough outline of the process (not tested) is as follows:

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

Demo on DBFiddle using variables, using CTE

这篇关于当记录向上或向下移动时，SQL Server 重新排序序列号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！