There are some items in a table, I hope that user can define the sort of these items by himself. So I add a field sortValue (Int ) to the table, the table maybe
表中有一些项目,我希望用户可以自己定义这些项目的种类。所以我在表中添加了一个字段sortValue(Int),表也许
name sortValue
Jack 3
Paul 1
April 30
Alic 25
And I use the following code to display items sort by use defined.
我使用以下代码显示按使用定义排序的项目。
Select name, sortValue from myTable order by sortValue
The result will be
结果将是
name sortValue
Paul 1
Jack 3
Alic 25
April 30
I find it's hard to change the order of these items by the data structures .
我发现很难通过数据结构来改变这些项目的顺序。
For example:
I hope the record (name=Alic, sortValue=25) is at the second position. I have to update the record as (name=Alic, sortValue=2).
我希望记录(name = Alic,sortValue = 25)位于第二位。我必须将记录更新为(name = Alic,sortValue = 2)。
The result will be
结果将是
name sortValue
Paul 1
Alic 2
Jack 3
April 30
If I need the record (name=April, sortValue=30) is at the second position next time, I find I have no sortValue for the record!
如果我需要记录(name = April,sortValue = 30)下次是第二个位置,我发现我没有记录的sortValue!
1 个解决方案
#1
2
You can use floating point type for sortValue column, thus you'll always have a space to insert a new value between two others.
您可以对sortValue列使用浮点类型,因此您将始终有空间在两个其他列之间插入新值。
# if record is between others
sortValue = (prevSortValue + nextSortValue)/2
# if record is last
sortValue = prevSortValue + 1
It is theoretically possible to meet the same problem as with integers when you run out of floating point precision. But in practice it highly depends on how often do you insert records between others. Especially when you have lot of inserts near the same record.
当你用完浮点精度时,理论上可以满足与整数相同的问题。但在实践中,它在很大程度上取决于您在其他人之间插入记录的频率。特别是当您在同一记录附近有大量插入时。
Updating existing records just to put a new record in correct place seems very inefficient because update is much more expensive operation than insert and requires locking for records, that you do not suppose to modify.
更新现有记录只是为了将新记录放在正确的位置似乎非常低效,因为更新比插入更昂贵的操作并且需要锁定记录,您不应该修改。
#1
2
You can use floating point type for sortValue column, thus you'll always have a space to insert a new value between two others.
您可以对sortValue列使用浮点类型,因此您将始终有空间在两个其他列之间插入新值。
# if record is between others
sortValue = (prevSortValue + nextSortValue)/2
# if record is last
sortValue = prevSortValue + 1
It is theoretically possible to meet the same problem as with integers when you run out of floating point precision. But in practice it highly depends on how often do you insert records between others. Especially when you have lot of inserts near the same record.
当你用完浮点精度时,理论上可以满足与整数相同的问题。但在实践中,它在很大程度上取决于您在其他人之间插入记录的频率。特别是当您在同一记录附近有大量插入时。
Updating existing records just to put a new record in correct place seems very inefficient because update is much more expensive operation than insert and requires locking for records, that you do not suppose to modify.
更新现有记录只是为了将新记录放在正确的位置似乎非常低效,因为更新比插入更昂贵的操作并且需要锁定记录,您不应该修改。