如何对MariaDB的SELECT查询结果进行聚类整合？

针对你的场景，不同数据库提供了对应的函数来将关联的多行数据聚合为嵌套的数组/JSON结构，避免主表数据重复。以下是主流数据库的实现方案：

PostgreSQL #

PostgreSQL 支持 json_agg（生成JSON数组）和 array_agg（生成普通数组）函数，结合 DISTINCT 处理多表关联产生的笛卡尔积问题：

SELECT
    t1.id,
    t1.name,
    -- 将关联的table2数据转为JSON数组
    json_agg(DISTINCT t2) AS table2_data,
    -- 将关联的table3数据转为JSON数组
    json_agg(DISTINCT t3) AS table3_data
FROM table1 t1
LEFT JOIN table1_table2 t1t2 ON t1.id = t1t2.table1_id
LEFT JOIN table2 t2 ON t1t2.table2_id = t2.id
LEFT JOIN table1_table3 t1t3 ON t1.id = t1t3.table1_id
LEFT JOIN table3 t3 ON t1t3.table3_id = t3.id
GROUP BY t1.id, t1.name;

如果只需要聚合单个字段（比如table2的name），可以用 array_agg(DISTINCT t2.name) 生成字符串数组。

MySQL 8.0+ #

MySQL 8.0及以上版本提供 JSON_ARRAYAGG 函数，建议通过子查询先分别聚合table2和table3的数据，再与主表关联，避免笛卡尔积导致的重复：

SELECT
    t1.id,
    t1.name,
    COALESCE(t2_arr.table2_data, '[]') AS table2_data,
    COALESCE(t3_arr.table3_data, '[]') AS table3_data
FROM table1 t1
LEFT JOIN (
    SELECT
        table1_id,
        JSON_ARRAYAGG(JSON_OBJECT('id', t2.id, 'name', t2.name)) AS table2_data
    FROM table1_table2 t1t2
    JOIN table2 t2 ON t1t2.table2_id = t2.id
    GROUP BY table1_id
) t2_arr ON t1.id = t2_arr.table1_id
LEFT JOIN (
    SELECT
        table1_id,
        JSON_ARRAYAGG(JSON_OBJECT('id', t3.id, 'name', t3.name)) AS table3_data
    FROM table1_table3 t1t3
    JOIN table3 t3 ON t1t3.table3_id = t3.id
    GROUP BY table1_id
) t3_arr ON t1.id = t3_arr.table1_id;

用 COALESCE 确保没有关联数据时返回空数组而非NULL。

SQL Server 2016+ #

SQL Server 2016及以上支持 FOR JSON PATH 语法，通过关联子查询直接生成嵌套的JSON数组：

SELECT
    t1.id,
    t1.name,
    -- 生成table2的JSON数组，无数据时返回NULL
    (SELECT t2.id, t2.name
     FROM table2 t2
     JOIN table1_table2 t1t2 ON t2.id = t1t2.table2_id
     WHERE t1t2.table1_id = t1.id
     FOR JSON PATH) AS table2_data,
    -- 生成table3的JSON数组，无数据时返回NULL
    (SELECT t3.id, t3.name
     FROM table3 t3
     JOIN table1_table3 t1t3 ON t3.id = t1t3.table3_id
     WHERE t1t3.table1_id = t1.id
     FOR JSON PATH) AS table3_data
FROM table1 t1;

如果需要空数组而非NULL，可以在子查询外层用 ISNULL(..., '[]') 处理。

关键注意事项 #

• 笛卡尔积问题：直接关联多张表会导致table2和table3的数据交叉，产生重复条目，因此要么用 DISTINCT 聚合去重，要么先子查询分别聚合再关联主表。
• 数据库兼容性：上述函数均为各数据库的原生特性，没有通用的跨数据库语法，需根据你使用的数据库选择对应方案。

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