如何在SQL中表示并插入有序列表? [英] How to represent and insert into an ordered list in SQL?

问题描述

我想在 SQL 表中表示列表hi"、hello"、goodbye"、good day"、howdy"(按顺序):

pk |我|值------------1 |0 |嗨0 |2 |你好2 |3 |再见3 |4 |美好的一天5 |6 |你好

'pk' 是主键列.忽略它的价值.

'i' 是定义 'val' 列中值的顺序的索引".它仅用于建立顺序，否则值不重要.

我遇到的问题是在保持顺序的同时将值插入列表.例如，如果我想插入嘿"并且我希望它出现在hello"和goodbye"之间，那么我必须移动goodbye"和good"的i"值天"(但最好不要你好")为新条目腾出空间.

那么，是否有标准的 SQL 模式来进行移位操作，但只移位必要的元素?(请注意，简单的UPDATE table SET i=i+1 WHERE i>=3"不起作用，因为它违反了对 'i' 的唯一性约束，并且它也不必要地更新了howdy"行.)

或者，有没有更好的方法来表示有序列表?我想您可以将i"设为浮点值并在其中选择值，但是如果不存在这样的值，您必须进行单独的重新平衡操作.

或者，如果我将 'i' 设为 varchar，是否有一些标准算法可以在任意其他字符串之间生成字符串值?

或者我应该将它表示为一个链表?我避免这样做，因为我还希望能够执行 SELECT .. ORDER BY 以按顺序获取所有元素.

解决方案

您可以通过使用级联触发器轻松实现这一点，该触发器将任何与插入/更新操作中的新条目相等的索引"条目更新为索引值 +1.这将级联所有行，直到第一个间隙停止级联 - 请参阅 这篇博客文章 PostgreSQL 实现.

这种方法应该独立于所使用的 RDBMS 工作，前提是它支持触发器在更新/插入之前触发.如果您在代码中实现您想要的行为(增加所有以下索引值，直到遇到差距)，它基本上可以完成您所做的事情，但以一种更简单、更有效的方式.

或者，如果您可以接受 SQL Server 的限制，请查看 层次结构类型.虽然主要用于定义嵌套层次结构，但您也可以将其用于平面排序.它有点类似于您使用浮点数的方法，因为它允许通过分配小数值在两个位置之间插入，从而避免更新其他条目的需要.

I want to represent the list "hi", "hello", "goodbye", "good day", "howdy" (with that order), in a SQL table:

pk | i | val
------------
1  | 0 | hi
0  | 2 | hello
2  | 3 | goodbye
3  | 4 | good day
5  | 6 | howdy

'pk' is the primary key column. Disregard its values.

'i' is the "index" that defines that order of the values in the 'val' column. It is only used to establish the order and the values are otherwise unimportant.

The problem I'm having is with inserting values into the list while maintaining the order. For example, if I want to insert "hey" and I want it to appear between "hello" and "goodbye", then I have to shift the 'i' values of "goodbye" and "good day" (but preferably not "howdy") to make room for the new entry.

So, is there a standard SQL pattern to do the shift operation, but only shift the elements that are necessary? (Note that a simple "UPDATE table SET i=i+1 WHERE i>=3" doesn't work, because it violates the uniqueness constraint on 'i', and also it updates the "howdy" row unnecessarily.)

Or, is there a better way to represent the ordered list? I suppose you could make 'i' a floating point value and choose values between, but then you have to have a separate rebalancing operation when no such value exists.

Or, is there some standard algorithm for generating string values between arbitrary other strings, if I were to make 'i' a varchar?

Or should I just represent it as a linked list? I was avoiding that because I'd like to also be able to do a SELECT .. ORDER BY to get all the elements in order.

解决方案

You can easily achieve this by using a cascading trigger that updates any 'index' entry equal to the new one on the insert/update operation to the index value +1. This will cascade through all rows until the first gap stops the cascade - see the second example in this blog entry for a PostgreSQL implementation.

This approach should work independent of the RDBMS used, provided it offers support for triggers to fire before an update/insert. It basically does what you'd do if you implemented your desired behavior in code (increase all following index values until you encounter a gap), but in a simpler and more effective way.

Alternatively, if you can live with a restriction to SQL Server, check the hierarchyid type. While mainly geared at defining nested hierarchies, you can use it for flat ordering as well. It somewhat resembles your approach using floats, as it allows insertion between two positions by assigning fractional values, thus avoiding the need to update other entries.

这篇关于如何在SQL中表示并插入有序列表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！