基于同表字段求和高效更新表列及合并更新的优化方案

你当前的实现通过自连接子查询完成了分组求和更新，但确实可以通过**窗口函数（OVER子句）**来避免自连接，同时将两个更新操作合并为一次，让代码更简洁高效。

优化方案：使用窗口函数实现单次更新 #

利用SQL Server的窗口函数SUM() OVER()，可以直接在更新语句中计算分组求和结果，无需额外的子查询与表连接，一次性完成两个目标字段的更新：

IF OBJECT_ID('tempdb..#t1') IS NOT NULL DROP TABLE #t1
CREATE TABLE #t1 (
    id nvarchar(max), 
    astate varchar(16), 
    code varchar(16), 
    price decimal(16,2), 
    total_id_price_bystate decimal(16,2), 
    total_id_price decimal(16,2)
)

INSERT INTO #t1 VALUES 
   (100, 'CA', '0123', 123.01, null, null),
   (100, 'CA', '0124', 0.00, null, null),   
   (100, 'PA', '0256', 12.10, null, null),
   (200, 'MA', '0452', 145.00, null, null),
   (300, 'MA', '0578', 134.23, null, null),
   (400, 'CA', '1111', 94.12, null, null),
   (600, 'CA', '0000', 86.34, null, null),
   (500, 'CO', '1111', 0.00, null, null);

-- 单次更新完成两个字段的赋值
UPDATE #t1
SET 
    total_id_price_bystate = SUM(price) OVER(PARTITION BY id, astate),
    total_id_price = SUM(price) OVER(PARTITION BY id)

SELECT * FROM #t1

方案优势 #

• 消除自连接：不需要额外的子查询和表关联操作，降低逻辑复杂度的同时减少数据库IO开销
• 合并更新操作：原本两次更新合并为一次，仅扫描表一次，性能更优
• 代码更简洁：分组规则直接定义在更新语句中，逻辑清晰，可读性更强

原理说明 #

• SUM(price) OVER(PARTITION BY id, astate)：按id+astate组合分组，计算每组的price总和，对应原需求中的total_id_price_bystate
• SUM(price) OVER(PARTITION BY id)：仅按id分组，计算每个id下所有记录的price总和，对应原需求中的total_id_price

内容的提问来源于stack exchange，提问作者DizzleBeans