Oracle转SQL Server含聚合函数UPDATE语句报错求助

问题背景 #

Oracle中可正常运行的含聚合函数的UPDATE语句，迁移至SQL Server时触发报错：

An aggregate may not appear in the list of sets of an UPDATE statement

Oracle 原运行代码 #

UPDATE
  tab1
SET
  (t1.va, t1.vb) = (
    SELECT
      sum(
        CASE
          WHEN (t2.date2 - t2.date1) <= 30 THEN Coalesce((t2.x + t2.y), 0)
        END
      ) * 100,
      sum(
        CASE
          WHEN (t2.date2 - t2.date1) <= 60
          AND (t2.date2 - t2.date1) > 30 THEN Coalesce((t2.y + t2.z), 0)
        END
      ) * 100
    FROM
      tab2 t2
    WHERE
      t2.cd_xyz = tab1.cd_xyz
  );

SQL Server 报错代码 #

UPDATE
  tab1 t1
SET
  t1.va = sum(
    CASE
      WHEN (t2.date2 - t2.date1) <= 30 THEN COALESCE((t2.x + t2.y), 0)
    END
  ) * 100,
  t1.vb = sum(
    CASE
      WHEN (t2.date2 - t2.date1) <= 60
      AND (t2.date2 - t2.date1) > 30 THEN COALESCE((t2.y + t2.z), 0)
    END
  ) * 100
FROM
  tab2 t2
  INNER JOIN tab1 t1 ON t2.cd_xyz = t1.cd_xyz;

错误原因 #

SQL Server不允许在UPDATE的SET子句中直接使用未分组的聚合函数，原报错代码直接关联tab1和tab2后使用SUM，缺少明确的分组逻辑，导致语法校验失败；而Oracle的写法通过关联子查询，隐式按cd_xyz字段完成了分组聚合。

正确实现方式 #

方法1：预聚合子查询后关联更新 #

先对tab2按cd_xyz分组计算出需要的聚合值，再关联tab1进行更新：

UPDATE t1
SET
  t1.va = agg.va_val,
  t1.vb = agg.vb_val
FROM tab1 t1
INNER JOIN (
  SELECT
    t2.cd_xyz,
    SUM(CASE WHEN (t2.date2 - t2.date1) <= 30 THEN COALESCE(t2.x + t2.y, 0) END) * 100 AS va_val,
    SUM(CASE WHEN (t2.date2 - t2.date1) > 30 AND (t2.date2 - t2.date1) <= 60 THEN COALESCE(t2.y + t2.z, 0) END) * 100 AS vb_val
  FROM tab2 t2
  GROUP BY t2.cd_xyz
) agg ON agg.cd_xyz = t1.cd_xyz;

方法2：使用CTE（公共表表达式）实现 #

通过CTE预先计算聚合结果，再更新tab1：

WITH agg_data AS (
  SELECT
    t2.cd_xyz,
    SUM(CASE WHEN (t2.date2 - t2.date1) <= 30 THEN COALESCE(t2.x + t2.y, 0) END) * 100 AS va_val,
    SUM(CASE WHEN (t2.date2 - t2.date1) > 30 AND (t2.date2 - t2.date1) <= 60 THEN COALESCE(t2.y + t2.z, 0) END) * 100 AS vb_val
  FROM tab2 t2
  GROUP BY t2.cd_xyz
)
UPDATE t1
SET
  t1.va = ad.va_val,
  t1.vb = ad.vb_val
FROM tab1 t1
INNER JOIN agg_data ad ON ad.cd_xyz = t1.cd_xyz;

说明 #

两种方法均先完成tab2的分组聚合，确保聚合逻辑明确后再与tab1关联更新，符合SQL Server的语法规则，同时与原Oracle语句的业务逻辑完全一致。

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