基于grade分组更新table1的rate列为sums最大值报错求助

你的SQL语句报错的原因是：子查询(select MAX(sums) OVER(PARTITION BY grade) FROM table1)会返回与原表行数相同的结果集（每行对应其所在grade分组的sums最大值），而UPDATE语句的SET子句需要为每一行匹配唯一的赋值，直接使用这个子查询会导致数据库无法确定该行应该取哪个值，从而触发“子查询返回多行”的错误。

以下是几种可行的修正方案，适配不同数据库环境：

方案1：关联子查询 #

这种写法适用于几乎所有支持SQL的数据库，逻辑是为每一行匹配对应grade分组的sums最大值：

UPDATE table1
SET rate = (
    SELECT MAX(sums)
    FROM table1 t
    WHERE t.grade = table1.grade
);

方案2：分组后关联更新 #

先预计算每个grade的sums最大值，再通过关联更新原表，这种写法在数据量较大时效率更高：

-- MySQL 8.0+、PostgreSQL、SQL Server 通用写法
UPDATE table1
JOIN (
    SELECT grade, MAX(sums) AS max_sums
    FROM table1
    GROUP BY grade
) t ON table1.grade = t.grade
SET rate = t.max_sums;

方案3：CTE（公共表表达式）更新 #

适用于支持CTE的数据库（如PostgreSQL、SQL Server），逻辑更清晰：

WITH grade_max AS (
    SELECT grade, MAX(sums) AS max_sums
    FROM table1
    GROUP BY grade
)
UPDATE table1
SET rate = grade_max.max_sums
FROM grade_max
WHERE table1.grade = grade_max.grade;

执行以上任意一种语句后，就能将rate列更新为对应grade分组内sums列的最大值，得到你期望的结果。

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