单SQL查询关联多表并将多结果合并为数组列

嘿，这个需求很好实现，用SQL的JSON聚合函数就能搞定单语句关联三张表并生成你要的数组格式结果。我分主流数据库给你写具体实现：

我们需要通过LEFT JOIN关联三张表，再用JSON聚合函数把每个公司对应的分支数据合并成数组。下面是两种常用数据库的实现方式：

PostgreSQL 版本 #

SELECT
    c.id,
    c.name,
    json_agg(
        json_build_object(
            'id', cb.id,
            'city_name', ci.name
        )
    ) AS branches
FROM companies c
LEFT JOIN company_branches cb ON c.id = cb.company_id
LEFT JOIN cities ci ON cb.city_id = ci.id
GROUP BY c.id, c.name
ORDER BY c.id;

关键逻辑说明： #

• LEFT JOIN：保证即使某个公司没有分支，也会被包含在结果里（如果有这种场景的话）
• json_build_object：把分支ID和对应城市名称组装成单个JSON对象
• json_agg：将当前公司的所有分支对象聚合为一个JSON数组
• GROUP BY：按公司的ID和名称分组，确保每个公司只返回一行数据

MySQL 8.0+ 版本 #

如果用的是MySQL 8.0及以上版本（支持JSON聚合函数），可以用下面的语句：

SELECT
    c.id,
    c.name,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'id', cb.id,
            'city_name', ci.name
        )
    ) AS branches
FROM companies c
LEFT JOIN company_branches cb ON c.id = cb.company_id
LEFT JOIN cities ci ON cb.city_id = ci.id
GROUP BY c.id, c.name
ORDER BY c.id;

关键逻辑说明： #

• JSON_OBJECT：和PostgreSQL的json_build_object作用一致，用来构建分支的JSON对象
• JSON_ARRAYAGG：把多个分支对象聚合为一个JSON数组
• 同样通过LEFT JOIN和GROUP BY保证结果的结构符合预期

执行结果 #

不管用上面哪种语句，执行后都会得到你期望的格式：

[
 {"id": 1, "name": "microsoft", "branches": [{"id": 2, "city_name": "New york"}, {"id": 3, "city_name": "Chicago"}]},
 {"id": 2, "name": "facebook", "branches": [{"id": 1, "city_name": "LA"}]}
]

要是你用的是其他数据库（比如SQL Server、Oracle），可以告诉我，我再给你调整对应的语法~

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