假设我们有以下两个表格:
问题表格(question):
id | question_text | answer_id |
---|---|---|
1 | What is the capital of China? | 1 |
2 | What is 2 + 2? | 3 |
答案表格(answer):
id | answer_text |
---|---|
1 | Beijing |
2 | Shanghai |
3 | 4 |
4 | 5 |
5 | 6 |
我们可以使用以下 SQL 查询,将每个问题和其相关的所有选项和答案合并到同一个 JSON 对象中:
SELECT
q.question_text,
JSON_OBJECTAGG(a.id, a.answer_text) AS choices,
a.answer_text AS correct_answer
FROM question q
JOIN answer a
ON q.answer_id = a.id
JOIN answer a2
ON q.answer_id = a2.id
GROUP BY q.question_text, a.answer_text;
此查询使用了 JOIN 和 GROUP BY 语句来将所有相关的行合并到一个 JSON 对象中。JSON_OBJECTAGG() 是 MySQL 中的一个聚合函数,可以将键值对列表转换为 JSON 格式。
查询结果如下:
question_text | choices | correct_answer |
---|---|---|
What is 2 + 2? | {"3": "4", "4": "5", "5": "6"} | 4 |
What is the capital of China? | {"1": "Beijing", "2": "Shanghai", "3": "Xian"} | Beijing |
可以看到,所有问题选项和答案都已经合并到了同一个 JSON 对象中。