PostgreSQL JOIN技术咨询：统计指定ID出现次数并写入表B新列

没问题，我来帮你搞定这个需求！要实现统计表a中每个ID的评论次数，并把结果更新到表b的total_comments列，我们可以用PostgreSQL的UPDATE结合子查询或者关联查询来实现，下面给你两种常用的方案：

方案一：关联子查询（简洁直观） #

这种方式直接针对表b的每一行，查询对应ID在表a中的评论总数，逻辑简单易懂：

-- 直接更新，表a中无对应ID的行，total_comments会设为NULL
UPDATE table_b b
SET total_comments = (
    SELECT COUNT(*)
    FROM table_a a
    WHERE a.id = b.id
);

-- 如果希望无评论的ID的total_comments显示为0，用COALESCE处理空值
UPDATE table_b b
SET total_comments = COALESCE(
    (SELECT COUNT(*) FROM table_a a WHERE a.id = b.id),
    0
);

方案二：UPDATE FROM批量更新（高效） #

先提前统计好表a中所有ID的评论数，再关联表b进行批量更新，数据量大的时候性能更优：

-- 先统计每个ID的评论数，再关联更新表b
UPDATE table_b b
SET total_comments = a.comment_count
FROM (
    -- 子查询统计表a中每个ID的评论次数
    SELECT id, COUNT(*) AS comment_count
    FROM table_a
    GROUP BY id
) a
-- 通过ID关联两张表
WHERE b.id = a.id;

-- 若要处理表a中不存在的ID，将其total_comments设为0
UPDATE table_b b
SET total_comments = COALESCE(a.comment_count, 0)
FROM table_b b_left
LEFT JOIN (
    SELECT id, COUNT(*) AS comment_count
    FROM table_a
    GROUP BY id
) a ON b_left.id = a.id
WHERE b.id = b_left.id;

针对你的示例数据的执行效果 #

执行上面的语句后，表b的最终数据会变成：

（注：原表b中id=2的total_comments初始值为2，更新后会被替换为表a中id=2的实际评论数1）

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