如何在JOIN查询后实现歌曲对应乐队合并展示？GROUP BY报错解决

你遇到的问题很常见——要把同一首歌对应的多个乐队名称合并成一行，用逗号分隔。之前直接按Song.name分组出错，大概率是因为数据库的严格分组规则要求所有非聚合列都要出现在GROUP BY里，而且只按歌曲名称分组也可能存在同名不同ID的歌曲被错误合并的风险。

下面根据不同数据库给出具体的实现方案：

MySQL/MariaDB #

用GROUP_CONCAT函数来拼接字符串，记得在GROUP BY里同时包含歌曲的ID（避免同名歌曲混淆）：

SELECT Song.name AS Song, GROUP_CONCAT(Band.name SEPARATOR ', ') AS Band
FROM Song
JOIN BandOnSong ON Song.Id = BandOnSong.songId
JOIN Band ON BandOnSong.bandId = Band.Id
GROUP BY Song.name, Song.Id;

如果想让乐队名称按特定顺序排列，可以在GROUP_CONCAT里加ORDER BY，比如：GROUP_CONCAT(Band.name ORDER BY Band.name SEPARATOR ', ')

PostgreSQL #

使用STRING_AGG函数，语法更直观：

SELECT Song.name AS Song, STRING_AGG(Band.name, ', ') AS Band
FROM Song
JOIN BandOnSong ON Song.Id = BandOnSong.songId
JOIN Band ON BandOnSong.bandId = Band.Id
GROUP BY Song.name, Song.Id;

同样可以加排序：STRING_AGG(Band.name, ', ' ORDER BY Band.name)

SQL Server #

2017及以上版本 #

支持STRING_AGG，用法和PostgreSQL类似：

SELECT Song.name AS Song, STRING_AGG(Band.name, ', ') AS Band
FROM Song
JOIN BandOnSong ON Song.Id = BandOnSong.songId
JOIN Band ON BandOnSong.bandId = Band.Id
GROUP BY Song.name, Song.Id;

2016及以下版本 #

需要用STUFF结合FOR XML PATH的方式来拼接：

SELECT 
    s.name AS Song,
    STUFF((
        SELECT ', ' + b.name
        FROM BandOnSong bos
        JOIN Band b ON bos.bandId = b.Id
        WHERE bos.songId = s.Id
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS Band
FROM Song s
WHERE EXISTS (
    SELECT 1 FROM BandOnSong bos WHERE bos.songId = s.Id
);

Oracle #

使用LISTAGG函数，还能指定排序：

SELECT Song.name AS Song, LISTAGG(Band.name, ', ') WITHIN GROUP (ORDER BY Band.name) AS Band
FROM Song
JOIN BandOnSong ON Song.Id = BandOnSong.songId
JOIN Band ON BandOnSong.bandId = Band.Id
GROUP BY Song.name, Song.Id;

关于之前GROUP BY出错的原因 #

大多数现代数据库默认开启了严格分组模式（比如MySQL的ONLY_FULL_GROUP_BY），要求SELECT子句里所有未被聚合的列必须出现在GROUP BY中。你之前只按Song.name分组，但Song.Id并没有在GROUP BY里，所以会报错。加上Song.Id不仅能解决报错问题，还能避免不同ID但同名的歌曲被错误合并，让结果更准确。

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