PostgreSQL匹配双ID及日期条件统计另一表行数更新字段问题

你的SQL存在两处问题导致统计结果全部为0：

1. 内层统计子查询中误用了不存在的last_engagement_date字段，你从table1中查询的日期字段实际是last_date，因此日期判断条件始终不成立，统计结果恒为0
2. PostgreSQL的UPDATE语句关联额外数据集时需要显式加FROM子句，你原来的写法中没有声明FROM counted，无法正常关联CTE的统计结果

你可以选择简化后的关联统计写法，执行效率更高：

WITH counted AS (
    SELECT 
        t1.client_id,
        t1.member_id,
        COUNT(t2.created_at) AS new_email_count
    FROM table1 t1
    LEFT JOIN table2 t2 
        ON t1.client_id = t2.client_id 
        AND t1.member_id = t2.member_id 
        AND t2.created_at > t1.last_date
    GROUP BY t1.client_id, t1.member_id
)
UPDATE table1 t1
SET email_count = c.new_email_count
FROM counted c
WHERE t1.client_id = c.client_id 
AND t1.member_id = c.member_id;

如果你希望保留原有的标量子查询写法，仅修改错误点即可：

WITH counted AS (
    SELECT 
        t.client_id,
        t.member_id,
        (SELECT count(*)
         FROM table2 t2
         WHERE t2.client_id = t.client_id
           AND t2.member_id = t.member_id
           AND t2.created_at > t.last_date -- 替换错误的字段名
        ) AS count
    FROM table1 t
)
UPDATE table1
SET email_count = counted.count
FROM counted -- 补充FROM子句
WHERE table1.client_id = counted.client_id
AND table1.member_id = counted.member_id;

使用你提供的测试数据执行上述修正后的SQL，最终结果完全符合预期：

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