在数据库表中使用排序列

Question

假设我有一个“产品”表格，用于在购物网站的数据库中记录商店产品的描述、价格等信息。那么，使客户能够重新排序这些产品的最有效方法是什么？

我创建了一个“订单”列（整数），用于对记录进行排序，但这会导致性能方面的一些问题，因为我使用原始方法来更改实际需要更改的记录后面每条记录的顺序。例如：

Id    Order
5     3
8     1
26    2
32    5
120   4

我该怎么做才能把ID为26的记录的顺序改为3？

我的方法是创建一个过程，检查目标顺序（3）是否有记录，如果没有，则更新行的顺序（ID=26）。如果目标顺序中有记录，则使用该记录的ID作为参数执行该过程，并将其发送给目标顺序+1。

这会导致更新要更改的记录之后的每个记录以腾出空间:

Id    Order
5     4
8     1
26    3
32    6
120   5

那么一个更聪明的人会怎么做呢？

• 我使用SQL Server 2008 R2。

编辑：

我需要按照顺序列出项目的顺序，不涉及第二个关键字。仅使用“Order”列必须为其记录指定唯一位置。

除此之外，我想知道是否可以实现类似于链表的东西：使用“Next”列而不是“Order”列来保留下一个项的ID。但我不知道如何编写检索正确顺序的记录的查询。如果有人对这种方法有任何想法，请分享。

- Şafak Gür

---

一位经验更丰富的人不会将列命名为“Order”，因为这在SQL中已经是一个保留关键字.... :-) - marc_s

Answer 1

Update product set order = order+1 where order >= @value changed

虽然随着时间的推移，您的订单中会出现越来越大的“空间”，但它仍将“排序”。

这将在一个语句中为被更改的值和其后的每个值添加1，但上述语句仍然是正确的。在您的订单中可能会形成越来越大的“空格”，可能会超过INT值的限制。

考虑到不希望有空格的需求，可以给出另一种解决方案：

想象一个名为UpdateSortOrder的过程，其中参数为@NewOrderVal、@IDToChange、@OriginalOrderVal。

根据新/旧顺序向上或向下移动的情况，需要执行两个步骤的过程。

If @NewOrderVal < @OriginalOrderVal --Moving down chain 

--Create space for the movement; no point in changing the original 
    Update product set order = order+1 
    where order BETWEEN @NewOrderVal and @OriginalOrderVal-1;

end if

If @NewOrderVal > @OriginalOrderVal --Moving up chain

--Create space  for the momvement; no point in changing the original  
  Update product set order = order-1 
  where order between @OriginalOrderVal+1 and @NewOrderVal
end if

--Finally update the one we moved to correct value

    update product set order = @newOrderVal where ID=@IDToChange;

关于最佳实践问题；我所处的大多数环境通常希望按类别分组并按字母顺序或“热销程度”排序，因此不需要提供用户定义的排序。

- xQbert

---

确实是个好解决方案。谢谢。不过我还有一个问题：在第二个例子中，您在更新所需更改的记录之前更新了中间的记录。这意味着有两行在某一时刻共享相同的订单值。如果我的Order列上有唯一约束条件，我该如何做到这一点？ - Şafak Gür

不知道唯一约束，可以在此期间将其关闭； 或通过设置“update product set order = -1 where ID = @IDtoChange;”来启动过程，然后我们最后要做的是将-1设置为我们关心的值。 通过上面的更新。 不幸的是，这假定-1是一个允许的值，并且在这种情况下具有“特殊含义”。 （但只有在运行应该处于事务逻辑中的过程的零点几秒钟内）但由于我们的更新基于ID，并且处于事务中，-1不应导致违反约束。 - xQbert

“BETWEEN”默认是不对称的，因此例如“BETWEEN 3 AND 2”会返回一个空结果。我已经编辑了答案以反映这一点。 - törzsmókus

猜测在我的情况下这不起作用，我不知道新值。我只需要在删除中间行后“清理”排序顺序表。 - daniel_aren

在这种情况下，我会在删除后编写一个简单的更新。您知道（应该知道或可以获得）已删除记录的 sortOrder，因此只需执行以下操作.. Update tableName Set SortOrder = SortOrder-1 where SortOrder > @SortOrderDeleted 这将在删除记录时关闭间隙。如果删除了最后一条记录，则可能不会更新任何记录；但它仍将运行/执行，只是没有更改。 - xQbert

Answer 2

使用旧的技巧，BASIC 程序（以及其他地方）使用:通过在顺序列中跳过10或其他方便的增量来跳过数字。然后，您可以在两个相距10的现有数字之间插入单个行（实际上，如果您很幸运，可以插入最多9行）。或者，您可以将第370行移动到第565行，而无需更改从第570行开始的任何行。

- Jonathan Leffler

Answer 3

这里提供了一种使用常用表达式（CTE）的替代方法。

这种方法尊重SortOrder列上的唯一索引，会关闭任何可能由先前的DELETE操作留下的排序顺序中的间隔。

/* For example, move Product with id = 26 into position 3 */
DECLARE @id int = 26
DECLARE @sortOrder int = 3


;WITH Sorted AS (
    SELECT  Id,
            ROW_NUMBER() OVER (ORDER BY SortOrder) AS RowNumber
    FROM    Product
    WHERE   Id <> @id
)

UPDATE  p
SET     p.SortOrder = 
        (CASE 
            WHEN p.Id = @id THEN @sortOrder
            WHEN s.RowNumber >= @sortOrder THEN s.RowNumber + 1
            ELSE s.RowNumber
        END)
FROM    Product p
        LEFT JOIN Sorted s ON p.Id = s.Id 

- cwills

Answer 4

非常简单，您需要拥有"基数空缺"。

结构：您需要有2列：

1. pk = 32位整数 2. order = 64位的bigint（BIGINT，不是DOUBLE!!!）

插入/更新：

1. 当您插入第一条新记录时，必须设置order = round(max_bigint / 2)。 2. 如果在表格开头插入，则必须设置order = round("第一条记录的order"/2) 3. 如果在表格末尾插入，则必须设置order = round("max_bigint - 最后一条记录的order" / 2) 4. 如果在中间插入，则必须设置order = round("前一条记录的order- 后一条记录的order" / 2) 这种方法有非常大的基数。如果您遇到约束错误或认为您的基数很小，可以重新构建order列（标准化）。

在标准化的最大情况下（使用此结构），您可以在32位中拥有“基数空缺”。

这非常简单和快速！

请记住，只能使用INT！order是精度值！

- user2382679

---

@AndyM：这不是一个新问题，而是试图回答所给出的问题。 - cfi

但是仅插入64行后，您将用完“order”值（order 值将小于1）。 - Igor Vujovic

Answer 5

我过去使用过一种解决方案，取得了一定的成功，那就是使用“权重”而不是“顺序”。 权重显然是越重的项目（即数字越低）下沉到底部，越轻的项目（数字越高）上升到顶部。

如果我有多个具有相同权重的项目，则假定它们具有相同的重要性，并按字母顺序排序。

这意味着您的SQL将类似于以下内容：

ORDER BY 'weight', 'itemName'

希望这可以帮到您。

- PseudoNinja

---

谢谢。这是一个解决方案，但与我的当前方法没有区别。不过这是我的错，因为我应该明确指出需要使订单列唯一。（我会立即更新问题）我需要客户能够精确选择哪个项目排在第一位。在这种情况下，我没有像“名称”这样的次要条件，所以必须仅用“顺序”。如果我有两个权重分别为10和11的项目，如何使记录出现在它们之间而不更改它们的权重（从而更改它们后面的权重）是我的实际问题。 - Şafak Gür

订单必须是唯一且无间隙的吗？如果是，为什么？所述技术可以在列上完全具有唯一约束条件，因此顺序是唯一的。 - Jonathan Leffler

抱歉，我不明白。空隙不是问题。我只是不理解当两个项目重量相同时会发生什么，或者如何使一条记录出现在权重为10和11的两条记录之间（就像我在先前的评论中提到的）。特别是如果该列有唯一约束条件。在这种情况下，我必须更改其他项目的权重之一。您是在建议我不应该增加/减少1的权重吗？如果我漏掉了什么显而易见的东西，我向您道歉。 - Şafak Gür

我通常按照10的增量进行加权，这样如果我需要把东西嵌入到特定位置，我就有足够的空间了。 - PseudoNinja

为什么在排序列中必须使用 int 作为数据类型？如果使用浮点数，10和11之间有足够的空间... - charlesdeb

Answer 6

我正在开发一种具有树形结构且需要排序的数据库。我使用类似于链接列表的方法，该方法将在客户端上进行排序（而不是数据库）。可以通过递归查询在数据库中完成排序，但这对于此项目并非必要。

我制作了这份文档，介绍了我们如何实现排序顺序的存储，包括postgresql中的示例。请随意评论！ https://docs.google.com/document/d/14WuVyGk6ffYyrTzuypY38aIXZIs8H-HbA81st-syFFI/edit?usp=sharing

- Elmer

---

+1，不错的示例。我有两个问题：第一个问题： 为什么你同时拥有Parent和Preceeding - 为什么使用双向链表而不是单向链表？ 第二个问题： 如何选择有序记录：例如，按其父/子关系排序选择前100条记录。 - Şafak Gür

parent 指树形结构中的父节点。前面的列定义了节点的顺序。我最近在文档中更新了一个示例，使用递归公共表达式的 SELECT 获取节点的正确顺序。 - Elmer