SQL Server自定义记录在表中排序，允许删除记录 [英] SQL Server custom record sort in table, allowing to delete records

问题描述

SQL Server表具有以下列: ID (PK，自动递增)，订单号， Col1 ， Col2 ..

SQL Server table with custom sort has columns: ID (PK, auto-increment), OrderNumber, Col1, Col2..

默认情况下，插入触发器会按照建议的此处将值从ID复制到OrderNumber. 使用某些可视界面，用户可以通过递增或递减OrderNumber值来对记录进行排序.

By default an insert trigger copies value from ID to OrderNumber as suggested here. Using some visual interface, user can sort records by incrementing or decrementing OrderNumber values.

但是，与此同时如何处理被删除的记录呢?

However, how to deal with records being deleted in the meantime?

示例: 假设您添加记录的PK ID:1,2,3,4,5-OrderNumber接收相同的值.然后删除ID = 4，ID = 5的记录.下一条记录将具有ID = 6，并且OrderNumber将获得相同的值. 如果缺少2个OrderNumber，将迫使用户递减ID = 6的记录，例如3次以更改其订单(即，按下3x按钮).

Example: Say you add records with PK ID: 1,2,3,4,5 - OrderNumber receives same values. Then you delete records with ID=4,ID=5. Next record will have ID=6 and OrderNumber will receive the same value. Having a span of 2 missing OrderNumbers would force user to decrement record with ID=6 like 3 times to change it's order (i.e. 3x button pressed).

或者，可以将select count(*) from table插入OrderNumber中，但是当删除一些旧行时，它可以在表中具有多个相似的值.

Alternatively, one could insert select count(*) from table into OrderNumber, but it would allow to have several similar values in table, when some old rows are deleted.

如果不删除记录，而只是停用"它们，则它们仍按排序顺序包括在内，对用户而言是不可见的.目前，需要用Java解决方案，但是我认为这个问题与语言无关.

If one doesn't delete records, but only "deactivate" them, they're still included in sort order, just invisible for user. At the moment, solution in Java is needed, but I think the issue is language-independent.

有更好的方法吗?

推荐答案

我将简单地修改切换OrderNumber值的脚本，以便正确正确地执行脚本，而无需依赖它们之间的间隔.

I would simply modify the script that switches the OrderNumber values so it does it correctly without relying on their being without gaps.

我不知道您的脚本接受哪些参数以及如何使用它们，但是我最终想出的参数接受要移动的项的ID和要移动的位置数(负值)表示朝着较低的OrderNumber值"，而正值表示相反的方向.

I don't know what arguments your script accepts and how it uses them, but the one that I've eventually come up with accept the ID of the item to move and the number of positions to move by (a negative value would mean "toward the lower OrderNumber values", and a positive one would imply the opposite direction).

想法如下:

1. 查找指定项目的OrderNumber.

按第二个参数确定的方向排列从OrderNumber开始的所有项目.这样，指定的项目将获得1的排名.

Rank all the items starting from OrderNumber in the direction determined by the second argument. The specified item thus receives the ranking of 1.

选择等级从1到第二个参数的绝对值加1的项目. (即，最后一项是将指定项移动到的项.)

Pick the items with rankings from 1 to the one that is the absolute value of the second argument plus one. (I.e. the last item is the one where the specified item is being moved to.)

将结果集与自身连接起来，以便每一行都与下一行相连，最后一行与第一行相连，从而使用一组行来更新另一行.

Join the resulting set with itself so that every row is joined with the next one and the last row is joined with the first one and thus use one set of rows to update the other.

这是实现以上内容的查询，并带有注释，解释了一些棘手的部分:

This is the query that implements the above, with comments explaining some tricky parts:

_{已解决了重新排序不正确的问题}

/* these are the arguments of the query */
DECLARE @ID int, @JumpBy int;
SET @ID = ...
SET @JumpBy = ...

DECLARE @OrderNumber int;
/* Step #1: Get OrderNumber of the specified item */
SELECT @OrderNumber = OrderNumber FROM atable WHERE ID = @ID;

WITH ranked AS (
  /* Step #2: rank rows including the specified item and those that are sorted
     either before or after it (depending on the value of @JumpBy */
  SELECT
    *,
    rnk = ROW_NUMBER() OVER (
      ORDER BY OrderNumber * SIGN(@JumpBy)
      /* this little "* SIGN(@JumpBy)" trick ensures that the
         top-ranked item will always be the one specified by @ID:
         * if we are selecting rows where OrderNumber >= @OrderNumber,
           the order will be by OrderNumber and @OrderNumber will be
           the smallest item (thus #1);
         * if we are selecting rows where OrderNumber <= @OrderNumber,
           the order becomes by -OrderNumber and @OrderNumber again
           becomes the top ranked item, because its negative counterpart,
           -@OrderNumber, will again be the smallest one
      */
    )
  FROM atable
  WHERE OrderNumber >= @OrderNumber AND @JumpBy > 0
     OR OrderNumber <= @OrderNumber AND @JumpBy < 0
),
affected AS (
  /* Step #3: select only rows that need be affected */
  SELECT *
  FROM ranked
  WHERE rnk BETWEEN 1 AND ABS(@JumpBy) + 1
)
/* Step #4: self-join and update */
UPDATE old
SET OrderNumber = new.OrderNumber
FROM affected old
  INNER JOIN affected new ON old.rnk = new.rnk % (ABS(@JumpBy) + 1) + 1
            /* if old.rnk = 1, the corresponding new.rnk is N,
               because 1 = N MOD N + 1  (N is ABS(@JumpBy)+1),
               for old.rnk = 2 the matching new.rnk is 1: 2 = 1 MOD N + 1,
               for 3, it's 2 etc.
               this condition could alternatively be written like this:
               new.rnk = (old.rnk + ABS(@JumpBy) - 1) % (ABS(@JumpBy) + 1) + 1
             */

注意:这假定使用SQL Server 2005或更高版本.

Note: this assumes SQL Server 2005 or later version.

此解决方案的一个已知问题是，如果无法将指定ID精确地移动指定数量的位置(例如，如果您想将最上面的行向上移动任意数量的位置)，它将无法正确地移动"行位置，或第二行乘以两个或多个位置等).

One known issue with this solution is that it will not "move" rows correctly if the specified ID cannot be moved exactly by the specified number of positions (for instance, if you want to move the topmost row up by any number of positions, or the second row by two or more positions etc.).

这篇关于SQL Server自定义记录在表中排序，允许删除记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！