如何从users、posts、comments表查询带作者及评论数据的所有帖子

正确SQL实现（MySQL 5.7+ 适用） #

SELECT 
  JSON_ARRAYAGG(
    JSON_OBJECT(
      'id', p.id,
      'text', p.text,
      'author', JSON_OBJECT(
        'id', u.id,
        'username', u.ofdb_username,
        'image', u.image
      ),
      'comments', IFNULL(c.comment_list, JSON_ARRAY(null)),
      'created_at', p.created_at,
      'updated_at', p.updated_at
    )
  ) AS posts
FROM posts p
LEFT JOIN users u ON p.users_id = u.id
LEFT JOIN (
  -- 子查询提前聚合每个帖子的评论数据
  SELECT 
    c.posts_id,
    JSON_ARRAYAGG(
      JSON_OBJECT(
        'id', c.id,
        'text', c.text,
        'author', JSON_OBJECT(
          'id', cu.id,
          'username', cu.ofdb_username,
          'image', cu.image
        ),
        'created_at', c.created_at,
        'updated_at', c.updated_at
      )
    ) AS comment_list
  FROM comments c
  LEFT JOIN users cu ON c.users_id = cu.id
  GROUP BY c.posts_id
) c ON p.id = c.posts_id

修改说明 #

• 解决帖子重复问题：新增子查询提前按posts_id分组聚合所有评论，每个帖子只会返回一条评论聚合结果，和posts表关联后不会出现帖子行重复的问题。
• 实现评论作者关联：在评论子查询中第二次关联users表（别名cu），单独获取每条评论对应的作者信息，封装到评论的JSON结构中。
• 优化JSON生成逻辑：替换原有的GROUP_CONCAT手动拼接JSON字符串的方式，直接使用MySQL原生JSON_ARRAYAGG函数生成数组结构，自动处理特殊字符转义，避免拼接出错。
• 兼容无评论场景：用IFNULL判断，若帖子没有评论时返回[null]，完全匹配你需要的输出格式。

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