MySQL音乐库系统：用GROUP_CONCAT结合关联表合并多行作曲家信息

一、修复作曲家合并问题 #

要实现单条音乐记录对应合并后的作曲家列表，需通过GROUP BY对音乐的唯一标识（如music_id）分组，结合GROUP_CONCAT函数合并同组内的作曲家名称，同时可指定分隔符、排序规则并处理空值。

修改后的查询语句：

SELECT
    mus.music_title AS title,
    mus.music_id,
    IFNULL(mus.music_drawer, '') AS drawer,
    IFNULL(mus.music_number, '') AS num,
    GROUP_CONCAT(IFNULL(com.composer_fullname, '') SEPARATOR '; ' ORDER BY com.composer_fullname) AS composer
FROM
    music AS mus
    LEFT JOIN musiccomposerlink AS mcl ON mus.music_id = mcl.music_id
    LEFT JOIN composers AS com ON mcl.composer_id = com.composer_id
WHERE
    mus.music_title LIKE '%650%'
GROUP BY
    mus.music_id, mus.music_title, mus.music_drawer, mus.music_number
ORDER BY
    title

关键说明： #

• GROUP_CONCAT中的ORDER BY保证合并后的作曲家名称按字母排序，和原查询逻辑一致
• GROUP BY需包含所有非聚合字段（音乐基础信息字段），避免MySQL的ONLY_FULL_GROUP_BY模式报错
• 保留LEFT JOIN确保无作曲家的音乐也能出现在结果中

二、添加编曲家列 #

假设存在对应的arranger表和关联表musicarrangerlink（结构与作曲家关联表一致），只需在查询中添加另一组关联和GROUP_CONCAT：

完整查询语句：

SELECT
    mus.music_title AS title,
    mus.music_id,
    IFNULL(mus.music_drawer, '') AS drawer,
    IFNULL(mus.music_number, '') AS num,
    GROUP_CONCAT(IFNULL(com.composer_fullname, '') SEPARATOR '; ' ORDER BY com.composer_fullname) AS composer,
    GROUP_CONCAT(IFNULL(arr.arranger_fullname, '') SEPARATOR '; ' ORDER BY arr.arranger_fullname) AS arranger
FROM
    music AS mus
    LEFT JOIN musiccomposerlink AS mcl ON mus.music_id = mcl.music_id
    LEFT JOIN composers AS com ON mcl.composer_id = com.composer_id
    LEFT JOIN musicarrangerlink AS mal ON mus.music_id = mal.music_id
    LEFT JOIN arranger AS arr ON mal.arranger_id = arr.arranger_id
WHERE
    mus.music_title LIKE '%650%'
GROUP BY
    mus.music_id, mus.music_title, mus.music_drawer, mus.music_number
ORDER BY
    title

注意事项： #

• 若编曲家关联表或字段名与假设不同，替换为实际表/字段名即可
• 继续使用LEFT JOIN保证无编曲家的音乐记录不会被过滤
• GROUP_CONCAT支持自定义分隔符，可按需修改SEPARATOR后的内容

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