如何使用ORDER BY更新TOP(n)以获得可预测的结果？

30 浏览
0 Comments

如何使用ORDER BY更新TOP(n)以获得可预测的结果？

我试图读取一个被用作队列的数据库表的前100个项目。在这个过程中，我尝试将这些项目标记为已完成，方法如下：\n

UPDATE TOP(@qty)
    QueueTable WITH (READPAST)  
SET 
    IsDone = 1
OUTPUT
    inserted.Id,
    inserted.Etc
FROM
    QueueTable 
WHERE
    IsDone = 0
ORDER BY
    CreatedDate ASC;

\n唯一的问题是，根据MSDN上的UPDATE (Transact-SQL)，在UPDATE中ORDER BY是无效的，且：\n

\n在INSERT、UPDATE或DELETE中与TOP表达式一起使用的行没有以任何顺序排列。\n

\n我该如何实现我需要的操作，即更新队列顶部的项目并选择它们？

0

3 答案

0 Comments

问题出现的原因是在使用ORDER BY对TOP(n)进行更新时，结果可能是不确定的。解决方法是使用排名函数（例如row_number）对结果进行排序，以保证更新结果的可预测性。

以下是解决方法的具体实现：

update top (100) q
set IsDone = 1
output
    inserted.Id,
    inserted.Etc
from (
    select *, row_number() over(order by CreatedDate asc, (select 0)) rn
    from QueueTable) q
where rn <= 100

以上代码使用了排名函数row_number()，并通过对CreatedDate进行升序排序来保证更新结果的可预测性。在更新之前，首先将需要更新的数据进行排序，并为每一行数据分配一个排名。然后，通过设置WHERE子句来筛选出排名小于等于100的数据进行更新。更新操作完成后，可以通过OUTPUT子句来获取更新后的数据。

通过以上方法，我们可以在使用ORDER BY对TOP(n)进行更新时，获得可预测的结果。

0
0 Comments

在SSMS中经过测试，它可以正常工作。您可能需要根据需要进行一些修改。

原因：问题的出现是因为在使用UPDATE TOP(n)语句时，如果没有指定ORDER BY子句，更新的顺序是不确定的。这可能导致每次运行查询时都会得到不同的结果。

解决方法：为了使UPDATE TOP(n)语句具有可预测的结果，可以通过在UPDATE语句中添加ORDER BY子句来指定更新的顺序。以下是解决方法的代码示例：

-- 创建表结构
create table #temp1 (
    id int identity(1,1),
    value int
)
-- 插入示例数据
insert #temp1 values (1)
go 20
-- 解决方法
declare @n int = 10
declare @sql nvarchar(2000) =
N'update top '+ cast(@n as nvarchar(5)) +' #temp1
set value = 100
output inserted.value
where id in
(
    select top '+ cast(@n as nvarchar(5)) +' id from #temp1
    order by id
)'
execute sp_executesql @sql

通过在UPDATE语句中添加ORDER BY子句，并使用嵌套的SELECT语句来限制更新的记录数量，可以确保UPDATE TOP(n)语句的结果是可预测的。在上述示例中，我们通过指定ORDER BY id来按id字段的升序对记录进行排序，并使用嵌套的SELECT语句选择前10个id值进行更新。这样，每次运行查询时，都会按照相同的顺序更新相同的记录。

0
0 Comments

问题的原因是，用户想要在更新操作中使用ORDER BY子句来指定顺序，并且只更新前N条记录。用户希望能够预测更新的结果。

解决方法是在UPDATE语句中使用派生表、CTE或视图。首先，用户创建一个派生表或CTE，使用ORDER BY子句来指定顺序，并使用TOP(N)来限制记录的数量。然后，在UPDATE语句中使用FROM子句来引用这个派生表或CTE，并设置需要更新的列和输出的列。

下面是一个示例代码：

UPDATE x
SET 
    IsDone = 1
OUTPUT
    inserted.Id,
    inserted.Etc
FROM (
    SELECT TOP (N) *
    FROM
        QueueTable 
    WHERE
        IsDone = 0
    ORDER BY
        CreatedDate ASC
) x

这种方法比先计算一组ID再进行更新更快，并且通常具有更可取的锁定行为。

评论中提到，这种方法适用于任何表达式，并且在SQL Server 2005中引入了top ()。此外，这种方法的锁定行为与通常的更新操作相同，不需要额外的锁定操作。虽然这种方法没有官方文档支持，但很多人使用它并且经过验证。

，用户可以使用派生表、CTE或视图来实现在更新操作中使用ORDER BY子句和TOP(N)，并且可以预测更新的结果。这种方法在SQL Server 2005及更高版本中可用，并且具有与通常更新操作相同的锁定行为。

0