SQL Server 2019中如何更新表内匹配行计数至对应字段

正确解决方案 #

方法一：使用CTE+窗口函数 #

这是直观简便的方式，先通过窗口函数计算每个itemID的总行数，再关联原表完成更新：

WITH ItemCounts AS (
    SELECT 
        rownumber,
        COUNT(*) OVER (PARTITION BY itemID) AS total_count
    FROM mldatatest
)
UPDATE mldatatest
SET itemCount = ic.total_count
FROM mldatatest mt
JOIN ItemCounts ic ON mt.rownumber = ic.rownumber;

方法二：使用关联子查询 #

针对每行的itemID，查询对应分组的行数，确保子查询仅返回单个值：

UPDATE mt
SET itemCount = (
    SELECT COUNT(*) 
    FROM mldatatest 
    WHERE itemID = mt.itemID
)
FROM mldatatest mt;

你之前的错误原因 #

1. 自连接导致平方计数：第一次用left join mldatatest o on t.itemID = o.itemID会产生笛卡尔积（比如3条Banana行互相匹配，得到9条记录），所以count结果是正确值的平方。
2. 无关联子查询返回多行：更新语句中直接用(select count(*) from ... group by t.itemID)会返回3个结果（Banana:3、Apple:1、Pear:2），无法赋值给单个字段，因此报错Subquery returned more than 1 value。
3. 窗口函数的错误用法：单独用COUNT(*) OVER (PARTITION BY itemID)查询是正确的，但SQL Server不支持直接在UPDATE的SET子句中使用无关联的窗口函数，必须通过CTE或关联查询传递计算结果。

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