如何使用关联子查询编写PostgreSQL表A的复杂UPDATE语句

可以通过聚合子查询+关联更新的方式实现需求，先对表B按a_id分组统计必要信息，再关联表A进行更新，比多次子查询更高效。

完整UPDATE语句 #

UPDATE A
SET c = CASE
           WHEN b_stats.has_x AND b_stats.has_y THEN b_stats.max_e
           ELSE NULL
        END
FROM (
    SELECT
        a_id,
        BOOL_OR(d = 'X') AS has_x,
        BOOL_OR(d = 'Y') AS has_y,
        MAX(e) AS max_e
    FROM B
    GROUP BY a_id
) AS b_stats
WHERE A.id = b_stats.a_id
OR NOT EXISTS (SELECT 1 FROM B WHERE B.a_id = A.id);

语句解释 #

1. 子查询b_stats：
   • 按a_id分组，统计每个A记录对应的B子记录情况：
     • BOOL_OR(d = 'X')：判断该分组下是否存在d='X'的记录，存在则返回true
     • BOOL_OR(d = 'Y')：判断该分组下是否存在d='Y'的记录，存在则返回true
     • MAX(e)：计算该分组下所有e字段的最大值
2. UPDATE逻辑：
   • 关联表A和b_stats，通过A.id = b_stats.a_id匹配对应记录
   • 使用CASE语句判断：当同时存在X和Y记录时，将A.c设为max_e，否则设为NULL
   • 最后的OR NOT EXISTS(...)确保没有对应B子记录的A行（比如A.id=1、2）也被更新为NULL，完全符合预期结果

验证结果 #

执行该语句后，表A的结果将与你给出的目标一致：

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