sql-server - 更新 SQL Server 表中除重复记录外的所有记录

Question

我有一个 SQL Server 表，其中一列 (object_id) 中有重复条目，例如:

+----+-----------+------------+
| id | object_id | status_val |
+----+-----------+------------+
|  1 |         1 |          0 | 
|  2 |         1 |          0 | 
|  3 |         1 |          0 | 
|  4 |         2 |          0 | 
|  5 |         3 |          0 | 
|  6 |         3 |          0 | 
+----+-----------+------------+

我需要更新它们的所有状态，但 object_id 列中有重复的状态除外。因此，在上表中，object_id 1 和 3 是重复的。所以我想将它们的 status_val 更改为 2，除了其中一个条目。结果如下:

| id | object_id | status_val |
+----+-----------+------------+
|  1 |         1 |          0 | 
|  2 |         1 |          2 | 
|  3 |         1 |          2 | 
|  4 |         2 |          0 | 
|  5 |         3 |          0 | 
|  6 |         3 |          2 | 
+----+-----------+------------+

哪一个重复行的状态更新并不重要。

如有任何帮助，我们将不胜感激。

Answer 1

您可以在没有连接的情况下解决这个问题，这意味着它应该具有更好的性能。这个想法是按 object_id 对数据进行分组，计算每个 object_id 的行号。这就是“分区依据”的作用。然后您可以更新 row_num > 1 的位置。这将更新除第一个之外的所有重复的 object_id!

update t set t.status_val = 'some_status' 
from (
    select *, row_number() over(partition by object_id order by (select null)) row_num  
    from foo
) t 
where row_num > 1 

在包含 82944 条记录的测试表上，性能是这样的(您的里程可能会有所不同!):表“测试”。扫描计数 5、逻辑读取 82283、物理读取 0、预读读取 0、lob 逻辑读取 0、lob 物理读取 0、lob 预读读取 0。CPU 时间 = 141 毫秒，运行时间 = 150 毫秒。

我们当然也可以通过使用内部联接来解决这个问题，但是，通常这会导致更多的逻辑读取和更高的 CPU:

表“测试”。扫描计数 10，逻辑读取 83622，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。表“工作文件”。扫描计数 0、逻辑读取 0、物理读取 0、预读读取 0、lob 逻辑读取 0、lob 物理读取 0、lob 预读读取 0。表“工作台”。扫描计数 4、逻辑读取 167426、物理读取 0、预读读取 0、lob 逻辑读取 0、lob 物理读取 0、lob 预读读取 0。CPU 时间 = 342 毫秒，运行时间 = 233 毫秒。

循环结果并小批量更新:

declare @rowcount int = 1;
declare @batch_size int = 1000;

while @rowcount > 0 
begin
    update top(@batch_size) t set t.status_val = 'already updated'
    from (
        select *, row_number() over(partition by object_id order by (select null)) row_num  
        from foo
        where status_val <> 'already updated' 
    ) t 
    where row_num > 1 
    set @rowcount = @@rowcount;
end

如果其他并发 session 尝试访问此表，这将有助于保持锁定。