您可以使用GROUP_CONCAT:
SELECT person_id,
GROUP_CONCAT(hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;
正如路德维希在他的评论,你可以添加DISTINCT
运算符以避免重复:
SELECT person_id,
GROUP_CONCAT(DISTINCT hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;
正如 Jan 中所述他们的评论,您还可以使用以下方法在内爆之前对值进行排序ORDER BY
:
SELECT person_id,
GROUP_CONCAT(hobbies ORDER BY hobbies ASC SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;
正如达格在他的评论,结果有 1024 字节的限制。要解决此问题,请在查询之前运行此查询:
SET group_concat_max_len = 2048;
当然,你可以改变2048
根据您的需要。计算并分配该值:
SET group_concat_max_len = CAST(
(SELECT SUM(LENGTH(hobbies)) + COUNT(*) * LENGTH(', ')
FROM peoples_hobbies
GROUP BY person_id) AS UNSIGNED);