管理记录排序 [英] Manage records ordering

问题描述

我希望我的应用程序的用户使用名为 Order 的文件更改记录的顺序.我生成了一些脚本以更改记录的顺序，但我认为应该有更优化的方法来做到这一点.

I want the user of my application to change the order of records using a filed called Order. I generated some scripts in order to change the order of the records but I think there should be a more optimized way to do it.

我的测试表叫做 MyTable.表的ID称为ID，订单字段称为Order.

My test table is called MyTable. The ID of the table is called ID and the order field is called Order.

我的 SQL 命令如下:

My SQL commands are the following:

向上移动

Declare @ID int = 3;
Declare @Order int = (SELECT [Order] FROM MyTable WHERE ID = @ID);
Declare @PreviousID int = (SELECT MAX(ID) FROM MyTable WHERE [Order] < @Order);
Update MyTable SET [Order] = @Order - 1 WHERE ID = @ID;
Update MyTable SET [Order] = @Order WHERE ID = @PreviousID

下移

Declare @ID int = 3;
Declare @Order int = (SELECT [Order] FROM MyTable WHERE ID = @ID);
Declare @NextID int = (SELECT MIN(ID) FROM MyTable WHERE [Order] > @Order);
Update MyTable SET [Order] = @Order + 1 WHERE ID = @ID;
Update MyTable SET [Order] = @Order WHERE ID = @NextID

移到顶部

Declare @ID int = 3;
Declare @MinimumOrder int = (SELECT Min([Order]) FROM MyTable);
Update MyTable SET [Order] = @MinimumOrder - 1 WHERE ID = @ID;

移至底部

Declare @ID int = 3;
Declare @MaximumOrder int = (SELECT Max([Order]) FROM MyTable);
Update MyTable SET [Order] = @MaximumOrder + 1 WHERE ID = @ID;

这些 SQL 命令可以正常工作.订单字段也可以有负数.

These SQL commands work without problem. It also can have negative numbers for the Order field.

我还想再生成一个 SQL 脚本，该脚本将更新提交的订单，以便更新提交的订单，以便订单从 1 开始并将其值增加 1.这很有用，因为有时我们可能会删除记录或我的脚本可能会产生负订单号.例如，如果您尝试将 Order = 1 的记录向上移动，则 Order 将取值为 0，如果您再次这样做，它将取值为 -1，等等.

I also would like to generate one more SQL script which will update the Order filed so that it will update the Order filed so that the Order will start from 1 and increase it's value by 1. This is useful because sometimes we may delete records or my scripts may produce negative order numbers. If for example you try to move up the record with Order = 1, it will have as a result the Order to take the value 0 and if you do it again it will take value -1, etc.

推荐答案

T-SQL 的 ROW_NUMBER() 操作在合并订单值方面可能对您有用.

T-SQL's ROW_NUMBER() operation might be useful to you in terms of consolidating order values.

这是一个示例，说明您可以如何使用此功能来整合您的订单值，而不会影响该订单中商品的排名:

This is an example of how you can use this functionality to consolidate your order values without affecting the ranking of items within that order:

/* declare placeholder and populate with test values */
declare @MyTable table (ID bigint identity(1,1), [Order] bigint)

insert into @MyTable ([Order]) 
VALUES
(1),
(3),
(2),
(5),
(-4),
(13),
(0)

/* Look at values we've just inserted */
select * from @MyTable order by [Order]

/* Show how ROW_NUMBER() can apply a consolidated ranking based on our existing order */
select *, ROW_NUMBER() over (order by [Order] asc) as sort from @MyTable

/* Apply that consolidated ranking to update the order values */
update @MyTable
set [Order] = consolidated.sort
from 
(
select ID as refID, ROW_NUMBER() over (order by [Order] asc) as sort from @MyTable
) consolidated
where consolidated.refID = ID

/* Final display of updated table */
select * from @MyTable order by [Order]

不过，理想情况下，从一开始就花一些时间来管理和保持数据清洁是值得的.

Ideally, though, it's worth taking some time to manage and keep clean the data from the start.

其中一部分是数据库结构和规范化，例如:

Part of this is database structure and normalization, looking at things like:

• 记录可以存在而不被排序吗?
• 如果有多个用户，他们是否都使用相同的订单，还是每个用户都有单独的订单排名?
• 一个用户是否可能有多个订单想要使用和切换?

如果其中任何一个为真，您可能希望将排序分解到一个单独的表中以用于数据完整性目的(并且由于事务锁和其他事情，即使您不这样做也可能值得这样做)

If any of those are true, you might want to break the ordering out into a separate table for data integrity purposes (and because of transaction locks and things, it might be worthwhile to do even if you don't)

除了数据库设计之外，还值得看看您如何处理修改记录顺序的数据操作.

Database design aside, it's also worthwhile to look at how you handle the data operations for modifying record order.

如果我们使用一个包含 N 条记录的表，并且这些记录具有密集排列的顺序(按照上面的查询所示，顺序值为 1、2、3、4、5 等)，然后任何时候对该订单进行更改，我们都必须更新很多表中现有的订单值.

If we're using a table with N records, and these records have a densely-packed order (as per what the query above demonstrates, where the order values are 1,2,3,4,5,etc.), then any time we make a change to that order, we have to update a lot of the existing order values in the table.

例如:

• 用户修改了一条记录，使其顺序从 5 变为 2.我们现在必须将每条记录从顺序 2 移动到顺序的末尾.

• The user modifies a record so its order changes from 5 to 2. We now have to shift every record from order 2 onwards to the end of the order.

弥补这一点的一种方法是使用偏移量计算排名 - 而不是按 1,2,3... 排序，而是使用更大的值，如 10,20,30....这让您可以在不立即加载大量数据库的情况下处理用户的排序更改(将某些内容移到第 2 位?将其插入位置 15，即 10 到 20 之间)，然后您可以稍后优化排序.

One way to compensate for this is to calculate ranking using offsets - rather than ordering by 1,2,3... instead use larger values like 10,20,30.... This lets you handle the user's ordering changes without a lot of immediate database load (move something up to order 2? Slot it in at position 15, between 10 and 20), and you can then optimize the ordering later.

这篇关于管理记录排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！