如何用单SQL查询多表树形结构：获取用户关联的项目-看板-帖子数据

单条SQL查询层级数据（用户→项目→看板→帖子） #

你的场景是1对多的关联链（用户对应多个项目，每个项目对应多个看板，每个看板对应多个帖子），这种情况不需要用递归CTE（递归CTE适合处理树形层级数据，比如部门上下级、评论回复链），直接通过多表JOIN就能实现单条查询：

SELECT
  -- 用户数据
  u.user_id,
  u.nickname,
  u.theme,
  -- 项目数据
  pr.project_id,
  pr.time_created AS project_created,
  pr.time_last_modified AS project_last_modified,
  pr.title AS project_title,
  -- 看板数据
  b.board_id,
  b.title AS board_title,
  b.order_position,
  b.color,
  -- 帖子数据
  po.post_id,
  po.time_created AS post_created,
  po.title AS post_title,
  po.priority,
  po.time_due,
  po.body
FROM users u
INNER JOIN projects pr ON u.user_id = pr.fk_projects_users
INNER JOIN boards b ON pr.project_id = b.fk_boards_projects
INNER JOIN posts po ON b.board_id = po.fk_posts_boards -- 注意你原来的SQL这里条件写错了，应该关联外键
WHERE u.user_id = 'exampleid';

这个查询会返回所有匹配的行，但确实会有数据冗余：同一个用户信息会出现在所有关联的项目行里，同一个项目信息会出现在所有关联的看板行里，以此类推。

你的四种方案分析 #

方案1：单条JOIN查询（带冗余） #

• 优点：一次查询获取所有数据，数据库仅执行一次查询计划。
• 缺点：返回结果有冗余数据，传输量更大；应用层需要自行处理冗余，将重复的用户/项目/看板数据合并为层级结构。
• 适用场景：数据量不大，或应用层处理合并逻辑成本低的情况。

方案2：四次独立查询 #

• 优点：无数据冗余，每个查询返回对应表的独立数据，应用层可直接按层级组装。
• 缺点：需要发起4次数据库请求，增加网络交互开销；若有事务需求，需保证四次查询的数据一致性。
• 注意：你提供的帖子查询SQL中，ON po.post_id = b.board_id是错误条件，应改为ON po.fk_posts_boards = b.board_id，否则关联逻辑失效。

方案3：给所有表加user_id字段 #

• 不推荐。这种做法违反数据库设计的第三范式，会产生大量数据冗余（比如同一个项目关联的所有看板，其user_id都与项目重复），且后续若用户转移项目所有权，需批量更新所有关联的看板、帖子的user_id，维护成本极高，易出现数据不一致问题。

方案4：JSON聚合查询（推荐） #

这是更优的方案，既能一次查询获取所有数据，又能返回无冗余的层级结构，无需应用层处理重复数据。以下是两个主流数据库的示例：

PostgreSQL 示例

SELECT
  json_build_object(
    'user_id', u.user_id,
    'nickname', u.nickname,
    'theme', u.theme,
    'projects', json_agg(
      json_build_object(
        'project_id', pr.project_id,
        'project_created', pr.time_created,
        'project_last_modified', pr.time_last_modified,
        'project_title', pr.title,
        'boards', json_agg(
          json_build_object(
            'board_id', b.board_id,
            'board_title', b.title,
            'order_position', b.order_position,
            'color', b.color,
            'posts', json_agg(
              json_build_object(
                'post_id', po.post_id,
                'post_created', po.time_created,
                'post_title', po.title,
                'priority', po.priority,
                'time_due', po.time_due,
                'body', po.body
              )
            )
          )
        )
      )
    )
  ) AS user_data
FROM users u
LEFT JOIN projects pr ON u.user_id = pr.fk_projects_users
LEFT JOIN boards b ON pr.project_id = b.fk_boards_projects
LEFT JOIN posts po ON b.board_id = po.fk_posts_boards
WHERE u.user_id = 'exampleid'
GROUP BY u.user_id, u.nickname, u.theme;

MySQL 8.0+ 示例

SELECT
  JSON_OBJECT(
    'user_id', u.user_id,
    'nickname', u.nickname,
    'theme', u.theme,
    'projects', JSON_ARRAYAGG(
      JSON_OBJECT(
        'project_id', pr.project_id,
        'project_created', pr.time_created,
        'project_last_modified', pr.time_last_modified,
        'project_title', pr.title,
        'boards', (
          SELECT JSON_ARRAYAGG(
            JSON_OBJECT(
              'board_id', b_inner.board_id,
              'board_title', b_inner.title,
              'order_position', b_inner.order_position,
              'color', b_inner.color,
              'posts', (
                SELECT JSON_ARRAYAGG(
                  JSON_OBJECT(
                    'post_id', po_inner.post_id,
                    'post_created', po_inner.time_created,
                    'post_title', po_inner.title,
                    'priority', po_inner.priority,
                    'time_due', po_inner.time_due,
                    'body', po_inner.body
                  )
                ) FROM posts po_inner
                WHERE po_inner.fk_posts_boards = b_inner.board_id
              )
            )
          ) FROM boards b_inner
          WHERE b_inner.fk_boards_projects = pr.project_id
        )
      )
    )
  ) AS user_data
FROM users u
LEFT JOIN projects pr ON u.user_id = pr.fk_projects_users
WHERE u.user_id = 'exampleid'
GROUP BY u.user_id, u.nickname, u.theme;

该方案返回嵌套的JSON结构，直接对应用户→项目→看板→帖子的层级关系，无冗余数据，应用层可直接解析使用。

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