管理记录排序

一尘不染

管理记录排序

sql

我希望我的应用程序的用户使用名为Order的文件来更改记录的顺序。我生成了一些脚本来更改记录的顺序，但是我认为应该有一种更优化的方法来执行此操作。

我的测试表称为MyTable。表的ID称为ID，订单字段称为Order。

我的SQL命令如下：

提升

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命令可以正常工作。“订单”字段也可以具有负数。

我还想再生成一个SQL脚本，该脚本将更新Order字段，以便更新Order字段，以便Order从1开始并将其值增加1。这很有用，因为有时我们可能会删除记录或脚本可能会产生负序号。例如，如果您尝试向上移动Order
= 1的记录，则结果Order的值将为0，如果再次执行，则其值将为-1，依此类推。

阅读 151

2021-03-17

共1个答案

一尘不染

在合并订单值方面，T-SQL的ROW_NUMBER（）操作可能对您有用。

这是一个示例，说明如何使用此功能合并订单值而不影响该订单中项目的排名：

/* 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]

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

其中一部分是数据库结构和规范化，其内容如下：

记录可以存在并且不能订购吗？
如果有多个用户，他们是否都使用相同的订单，还是每个用户都有单独的订单排名？
用户是否可能有多个要使用的订单并在它们之间进行切换？

如果其中任何一个是正确的，则出于数据完整性的目的，您可能希望将排序分解到一个单独的表中（并且由于事务锁定和其他原因，即使您不这样做，也可能值得这样做）

除了数据库设计之外，还值得研究如何处理数据操作以修改记录顺序。

如果我们使用具有N条记录的表，并且这些记录具有密集的顺序（如上面的查询所示，顺序值为1,2,3,4,5等），那么 任何当
我们对该订单进行更改时，我们必须更新表中 的许多 现有订单值。

例如：

用户修改了一条记录，因此其顺序从5更改为2。我们现在必须将 每条记录 从顺序2转移到顺序的末尾。

一种补偿方法是使用偏移量来计算排名-而1,2,3...不是使用较大的值（例如）进行排序10,20,30...。这样一来，您就可以处理用户的排序更改，而无需立即承担很多数据库负载（将某些命令移动到2之前？将其放置在位置15，介于10到20之间），然后可以优化以后的排序。

2021-03-17