多表关联SQL查询需求：获取用户及关联团队完整信息（含无团队用户）

当然可以实现你的需求，核心是利用数据库提供的JSON聚合函数，将团队的所有字段封装成JSON对象后聚合成数组，同时通过左连接保留所有用户（包括无关联团队的用户）。以下是主流数据库的具体实现方案：

通用思路 #

• 通过LEFT JOIN关联用户与团队关联表、团队表，确保所有用户都被返回
• 在关联团队表时添加俱乐部筛选条件，只匹配指定俱乐部的团队
• 使用JSON函数将每个团队的字段拼接成JSON对象，再聚合成数组

MySQL 8.0+ 版本 #

MySQL 8.0及以上支持JSON_OBJECT（生成单个JSON对象）和JSON_ARRAYAGG（聚合JSON对象为数组）：

SELECT 
  u.email,
  -- 无关联团队时返回空数组，避免null
  COALESCE(
    JSON_ARRAYAGG(
      CASE WHEN t.id IS NOT NULL THEN 
        JSON_OBJECT(
          'id', t.id,
          'title', t.title,
          'club', t.club
        ) 
      END
    ),
    JSON_ARRAY()
  ) AS teams
FROM USER u
LEFT JOIN TEAM_USER tu ON tu.user_id = u.id
-- 这里指定要筛选的俱乐部ID（示例为1，对应manchester）
LEFT JOIN TEAM t ON t.id = tu.team_id AND t.club = 1
GROUP BY u.id, u.email;

PostgreSQL 版本 #

PostgreSQL使用jsonb_build_object和json_agg实现，同时支持FILTER过滤无效记录：

SELECT 
  u.email,
  -- 无关联团队时返回空数组
  COALESCE(
    json_agg(
      jsonb_build_object(
        'id', t.id,
        'title', t.title,
        'club', t.club
      )
    ) FILTER (WHERE t.id IS NOT NULL),
    '[]'::jsonb
  ) AS teams
FROM "USER" u  -- USER是PostgreSQL关键字，需加双引号
LEFT JOIN TEAM_USER tu ON tu.user_id = u.id
LEFT JOIN TEAM t ON t.id = tu.team_id AND t.club = 1
GROUP BY u.id, u.email;

SQL Server 2016+ 版本 #

SQL Server可通过子查询结合FOR JSON PATH生成数组，或用STRING_AGG拼接JSON对象：

方案1：子查询生成JSON数组（推荐） #

SELECT 
  u.email,
  ISNULL(
    (SELECT 
       t.id AS id,
       t.title AS title,
       t.club AS club
     FROM TEAM_USER tu
     JOIN TEAM t ON t.id = tu.team_id AND t.club = 1
     WHERE tu.user_id = u.id
     FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ), '[]'
  ) AS teams
FROM [USER] u;  -- USER是SQL Server关键字，需加方括号

方案2：STRING_AGG拼接（SQL Server 2022+支持） #

SELECT 
  u.email,
  COALESCE(
    '[' + STRING_AGG(
      JSON_OBJECT('id': t.id, 'title': t.title, 'club': t.club),
      ','
    ) + ']',
    '[]'
  ) AS teams
FROM [USER] u
LEFT JOIN TEAM_USER tu ON tu.user_id = u.id
LEFT JOIN TEAM t ON t.id = tu.team_id AND t.club = 1
GROUP BY u.id, u.email;

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