如何对SQL多表查询结果进行JSON格式聚合整理

这个需求很常见，你可以通过两种方式实现：要么在SQL层面直接聚合，要么在应用代码里分组处理。下面分别给你详细说明：

方法1：SQL层面聚合（按数据库类型选择） #

不同数据库的字符串聚合函数有所差异，我给你列几种主流数据库的写法：

MySQL/MariaDB 使用 GROUP_CONCAT #

修改你的SQL，明确指定字段并分组，用GROUP_CONCAT把相同question_id的answer_option拼接成字符串：

SELECT
    a_t.id AS answer_test_id,
    q.id AS question_id,
    q.questions_text,
    GROUP_CONCAT(a_o.answer_option) AS answer_option,
    a_t.answer,
    a_o.answer_option_id -- 若该字段同一question_id下值一致可保留，否则需考虑聚合或移除
FROM questions q
JOIN answer_options a_o ON q.id = a_o.question_id
JOIN answer_test a_t ON a_o.answer_test_id = a_t.answer_option_id
GROUP BY q.id, q.questions_text, a_t.id, a_t.answer, a_o.answer_option_id;

查询会返回逗号分隔的字符串（比如Dog,Cat），之后你可以在应用代码里将其分割为数组。如果需要去重，可在GROUP_CONCAT中添加DISTINCT：GROUP_CONCAT(DISTINCT a_o.answer_option)。

PostgreSQL 使用 STRING_AGG #

PostgreSQL的写法类似，用STRING_AGG替代GROUP_CONCAT：

SELECT
    a_t.id AS answer_test_id,
    q.id AS question_id,
    q.questions_text,
    STRING_AGG(a_o.answer_option, ',') AS answer_option,
    a_t.answer,
    a_o.answer_option_id
FROM questions q
JOIN answer_options a_o ON q.id = a_o.question_id
JOIN answer_test a_t ON a_o.answer_test_id = a_t.answer_option_id
GROUP BY q.id, q.questions_text, a_t.id, a_t.answer, a_o.answer_option_id;

SQL Server 2017+ 使用 STRING_AGG #

SQL Server 2017及以上版本支持STRING_AGG，写法如下：

SELECT
    a_t.id AS answer_test_id,
    q.id AS question_id,
    q.questions_text,
    STRING_AGG(a_o.answer_option, ',') AS answer_option,
    a_t.answer,
    a_o.answer_option_id
FROM questions q
JOIN answer_options a_o ON q.id = a_o.question_id
JOIN answer_test a_t ON a_o.answer_test_id = a_t.answer_option_id
GROUP BY q.id, q.questions_text, a_t.id, a_t.answer, a_o.answer_option_id;

方法2：应用代码层面分组处理 #

如果你的项目需要兼容多种数据库，或者更灵活地生成数组格式，推荐在应用代码里处理。这里给你一个Python示例，其他语言逻辑类似：

import json

# 假设这是从数据库获取的原始结果
original_data = [
    {"id":"1","questions_text":"Who you are ? ","answer_option":"Dog","question_id":"1","answer_test_id":"1","answer":"1","answer_option_id":"1"},
    {"id":"1","questions_text":"What your car","answer_option":"Audi","question_id":"2","answer_test_id":"1","answer":"1","answer_option_id":"1"},
    {"id":"1","questions_text":"Who you are ?","answer_option":"Cat","question_id":"1","answer_test_id":"1","answer":"1","answer_option_id":"1"}
]

# 按question_id分组，合并answer_option
grouped_result = {}
for item in original_data:
    q_id = item["question_id"]
    if q_id not in grouped_result:
        # 初始化分组项，将answer_option转为列表
        grouped_item = item.copy()
        grouped_item["answer_option"] = [item["answer_option"]]
        grouped_result[q_id] = grouped_item
    else:
        # 追加answer_option到列表
        grouped_result[q_id]["answer_option"].append(item["answer_option"])

# 转换为最终的列表格式
final_data = list(grouped_result.values())
print(json.dumps(final_data, indent=2))

执行这段代码后，就能得到你期望的数组格式结果。

注意事项 #

• 原SQL中的SELECT *容易导致字段冲突（比如多个表都有id字段），建议明确指定需要的字段并给冲突字段起别名，避免数据混乱。
• 如果answer_option_id在同一question_id下有不同的值，你需要考虑是否要聚合该字段，或者直接移除它，否则分组后可能出现不符合预期的结果。

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