同表存储帖子与回复时 如何用SQL统计并更新回复计数字段

核心统计逻辑：所有回复记录的replyto字段存储了被回复的帖子ID，按replyto分组统计行数，就能得到每个帖子的实际回复总数。
你之前跨表更新的逻辑本身是成立的，但直接套用同表查询会触发绝大多数数据库的限制：不允许在UPDATE语句中，直接将被更新的表作为子查询的数据源，因此需要对同表查询的部分做隔离处理，以下是不同环境下的可用写法：

支持CTE的现代数据库（MySQL 8.0+、PostgreSQL、SQLite 3.33+、SQL Server等） #

用公共表表达式预先计算好所有帖子的回复计数，再关联更新，逻辑最清晰，可读性最好：

WITH reply_stats AS (
    SELECT
        replyto AS target_post_id,
        COUNT(*) AS reply_total
    FROM posts
    WHERE replyto IS NOT NULL
    GROUP BY replyto
)
UPDATE posts
SET replies = COALESCE(reply_stats.reply_total, 0)
FROM reply_stats
WHERE posts.id = reply_stats.target_post_id;

用COALESCE处理没有任何回复的帖子，将其replies值设为0，避免出现NULL值。

兼容旧版MySQL 5.x 等不支持CTE的环境 #

通过嵌套一层派生表的方式绕开同表更新限制，数据库会将嵌套的子查询结果物化为临时表，不会和外层更新的表产生读写冲突：

UPDATE posts
SET replies = COALESCE(
    (SELECT COUNT(*)
    FROM (SELECT replyto FROM posts) AS all_reply_records
    WHERE all_reply_records.replyto = posts.id),
    0
);

大表性能优化写法（支持UPDATE JOIN语法的数据库，如MySQL、MariaDB） #

通过JOIN关联预聚合的计数结果更新，比关联子查询的执行效率更高，适合数据量较大的表：

UPDATE posts p
LEFT JOIN (
    SELECT replyto, COUNT(*) AS cnt
    FROM posts
    WHERE replyto IS NOT NULL
    GROUP BY replyto
) AS reply_count ON p.id = reply_count.replyto
SET p.replies = COALESCE(reply_count.cnt, 0);

注意事项 #

• 不要直接套用跨表更新的写法写UPDATE posts SET replies = (SELECT COUNT(*) FROM posts WHERE replyto = posts.id)，该语句会在绝大多数数据库环境下抛出同表更新相关的错误。
• 如果业务中存在软删除的帖子，需要在统计回复数的子查询/CTE中加上对应的过滤条件（例如WHERE is_deleted = 0），避免统计到已删除的回复。

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