MySQL中如何统计项目下两层关联表的评论数量？

方案一：嵌套子查询（与现有写法风格匹配） #

直接在你已有的查询中新增一个子查询，通过items关联comments，统计每个项目对应的所有评论数量：

SELECT 
    p.*,
    (SELECT COUNT(*) FROM items WHERE project_id = p.id) items_count,
    (SELECT COUNT(c.id) FROM comments c JOIN items i ON c.item_id = i.id WHERE i.project_id = p.id) comments_count
FROM projects p

如果要确保没有评论的项目显示0而非NULL，可以用COALESCE包裹子查询：

SELECT 
    p.*,
    COALESCE((SELECT COUNT(*) FROM items WHERE project_id = p.id), 0) items_count,
    COALESCE((SELECT COUNT(c.id) FROM comments c JOIN items i ON c.item_id = i.id WHERE i.project_id = p.id), 0) comments_count
FROM projects p

方案二：预聚合JOIN（大场景性能更优） #

先对items和comments分别做聚合统计，再关联到projects，这种方式在数据量较大时性能更好：

SELECT 
    p.*,
    COALESCE(item_stats.items_count, 0) items_count,
    COALESCE(comment_stats.comments_count, 0) comments_count
FROM projects p
LEFT JOIN (
    SELECT project_id, COUNT(*) items_count
    FROM items
    GROUP BY project_id
) item_stats ON p.id = item_stats.project_id
LEFT JOIN (
    SELECT i.project_id, COUNT(c.id) comments_count
    FROM items i
    LEFT JOIN comments c ON i.id = c.item_id
    GROUP BY i.project_id
) comment_stats ON p.id = comment_stats.project_id

说明 #

• 方案一写法简洁，和你现有SQL逻辑一致，适合小型数据集；
• 方案二通过预聚合减少了重复查询，在数据量大时能显著提升效率，同时用COALESCE处理空值，保证结果更友好。

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