T-SQL中设置列等于值出现计数时遇子查询多值错误

你想要把[User]表的count列设置为对应col1值在表中的出现次数，期望得到的结果是：

id col1 count
--------------
1  a    3
2  a    3
3  a    3
4  b    2
5  b    2

你先执行的查询语句：

select count(col1) as repidck from [User] u group by u.id

虽然能正常运行，但其实逻辑就不对——按id分组的话，每个id对应一行，只要col1不为空，count(col1)的结果都是1，根本没统计出col1值的总出现次数。

然后你执行更新语句时出现了错误：

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

你忽略的核心问题是：你的子查询返回了多行结果，而=运算符要求右侧的子查询必须为每一行更新返回单个值。你写的子查询按id分组，会返回和表中记录数一样多的结果，数据库没办法把这些多行结果对应到每一行的更新上。

正确的更新写法 #

这里给你两种可行的方案：

方案1：关联子查询（适合小表）

针对每一行的col1值，统计整个表中该值的出现次数：

UPDATE [User]
SET [count] = (
    SELECT COUNT(*)
    FROM [User] u
    WHERE u.col1 = [User].col1
)

方案2：先统计再关联更新（适合大表，效率更高）

先用CTE统计出每个col1值的总次数，再通过关联更新表：

WITH ColCounts AS (
    SELECT col1, COUNT(*) AS cnt
    FROM [User]
    GROUP BY col1
)
UPDATE u
SET u.[count] = cc.cnt
FROM [User] u
JOIN ColCounts cc ON u.col1 = cc.col1

这两种写法都能准确把每个col1值的总出现次数填充到对应的count列里，达到你想要的效果。

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