你可以使用
GROUP_CONCAT
:
SELECT person_id,
GROUP_CONCAT(hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;
正如Ludwig在他的评论中所述,你可以添加DISTINCT
运算符以避免重复:
SELECT person_id,
GROUP_CONCAT(DISTINCT hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;
正如Jan在他们的评论中所述,您可以在使用ORDER BY
进行implode之前对值进行排序:
SELECT person_id,
GROUP_CONCAT(hobbies ORDER BY hobbies ASC SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;
正如Dag在他的评论中所述,结果存在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);