MySQL如何通过外键id选择多行？

Question

调查表: enter image description here

问题表: enter image description here

我的 sql 查询:

SELECT s.id, q.questionFROM questions qJOIN surveys s ON q.surveyId = s.id;

我的结果:

[
    {
        "id": 5,
        "question": "Which industry are you working at?"
    },
    {
        "id": 5,
        "question": "What is your company's highest annual revenue?"
    },
    {
        "id": 5,
        "question": "How long has your company been operated? updated"
    }
]

但我想要这样的输出:

[
    {
        "id": 5,
        "questions": [
            "Which industry are you working at?",
            "What is your company's highest annual revenue?",
            "How long has your company been operated?"
        ]
    }
]  

enter image description here

Answer 1

你想要的是一个GROUP BY

SELECT 
  s.id, 
  JSON_ARRAYAGG(q.question)
FROM 
  questions q 
JOIN 
  surveys s ON q.surveyId = s.id;
GROUP BY
  s.id

这可能会非常接近您想要的，但是您需要 MySQL 8.0.14 或更高版本。看看JSON_ARRAYAGG() .可以使用其他聚合函数，但您需要对结果做一些处理。

不过，GROUP BY 是必不可少的。