解析含聚合子查询的SQL UPDATE语句

我正在将退休数据分析师的代码及流程迁移为可通过界面执行的可重复程序，因结果涉及WHS（职业健康安全），需完全理解SQL逻辑后再重构。现有多条统计语句，其中核心SQL如下：

-- Count the number of accidents at each site location. Separate count by the severity of the accident.
UPDATE work_location_stats
SET (fatal,
     hostpital,
     medical_treatment,
     minor_injury) =
    (SELECT SUM(DECODE (a.acc_severity, 1, 1, 0)),
            SUM(DECODE (a.acc_severity, 1, 2, 0)),
            SUM(DECODE (a.acc_severity, 1, 3, 0)),
            SUM(DECODE (a.acc_severity, 1, 4, 0))
     FROM accidents a, accident_locations l
     WHERE EXISTS (SELECT 'x'
                   FROM accident_criteria ac
                   WHERE ac.criteria = 1
                     AND a.accident_id = ac.accident_id)
       AND a.accident_id = l.accident_id
       AND work_location_stats.location_id = l.location_id
       AND a.accident_date BETWEEN to_date('01/JUL/2023', 'DD-MON-YYYY') AND to_date('30-JUL-2025', 'DD-MON-YYYY'));

问题1：该SELECT语句中使用SUM聚合函数却未加GROUP BY子句，此写法的原理是什么？ #

这是相关标量子查询的特性：子查询通过work_location_stats.location_id = l.location_id关联外层的work_location_stats表，相当于对work_location_stats的每一行单独执行一次聚合统计。此时子查询的结果是针对当前行location_id的单一行汇总值，刚好匹配UPDATE要赋值的四个字段。不需要GROUP BY是因为关联条件已经限定了只统计对应地点的事故数据，本质是按外层的location_id隐式分组，每处理外层一行，子查询就返回该地点的统计结果。

问题2：能否将EXISTS子句转换为JOIN语句，以此优化查询性能？ #

完全可以，将EXISTS替换为INNER JOINaccident_criteria的写法，能让查询优化器更高效地处理关联逻辑，尤其是当accident_criteria表的accident_id和criteria字段有索引时，性能提升更明显。同时注意原SQL的DECODE函数存在bug：四个SUM的DECODE都判断a.acc_severity = 1，会导致所有字段统计的都是致命事故数量，应该分别对应1（致命）、2（住院）、3（医疗处理）、4（轻伤），重构时必须修正。改写后的子查询示例如下：

SELECT SUM(DECODE(a.acc_severity, 1, 1, 0)),
       SUM(DECODE(a.acc_severity, 2, 1, 0)),
       SUM(DECODE(a.acc_severity, 3, 1, 0)),
       SUM(DECODE(a.acc_severity, 4, 1, 0))
FROM accidents a
JOIN accident_locations l ON a.accident_id = l.accident_id
JOIN accident_criteria ac ON a.accident_id = ac.accident_id AND ac.criteria = 1
WHERE work_location_stats.location_id = l.location_id
  AND a.accident_date BETWEEN TO_DATE('01/JUL/2023', 'DD-MON-YYYY') AND TO_DATE('30-JUL-2025', 'DD-MON-YYYY')

问题3：work_location_stats表未显式关联，是否仅为聚合查询提供逐行过滤条件？ #

没错，这是相关子查询的典型用法：子查询中的work_location_stats.location_id是引用外层UPDATE语句的表字段，相当于对work_location_stats的每一行，都执行一次子查询，只统计和当前行location_id匹配的事故数据，最终将统计结果赋值给当前行的四个字段。这种写法会逐行处理work_location_stats的记录，子查询的过滤条件完全依赖外层行的字段值。

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