如何使用数据引用的* _id字段来分隔/格式化string_agg（）聚合数据？

Question

这个问题是我前一个问题的继续，可以找到here。

This SQLFiddle正在使用数据库结构&查询，我在下面描述。

数据库看起来像：

CREATE TABLE artistnames (
    artistname_id  SERIAL PRIMARY KEY, 
    artistname   TEXT UNIQUE NOT NULL 
); 

CREATE TABLE artistalias (
    artistalias_id  SERIAL PRIMARY KEY, 
    artistname_id  SERIAL REFERENCES artistnames (artistname_id), 
    artistalias   TEXT UNIQUE NOT NULL 
); 

CREATE TABLE songs (
    song_id    SERIAL PRIMARY KEY, 
    song    TEXT NOT NULL, 
    artistalias_id  SERIAL REFERENCES artistalias (artistalias_id) 
); 

• 一个艺术家（表artistnames）可以有零个，一个或多个别名
• 一个别名（表artistalias）属于一个艺术家
• 一个歌曲（表歌曲）有一个或多个艺术家（并且指的是他用来执行歌曲的艺术家的别名）

示例： 艺术家Francis Veigar也使用别名Francis Fat和Francis Fighter。一首歌歌1已经发布了艺术家的名字弗朗西斯Veigar，为另一首歌曲宋2他用化名弗朗西斯脂肪和第三首歌曲歌曲3他使用别名弗朗西斯战斗机唱，连同另一位艺术家Peeka Boo。

使用查询

SELECT 
    string_agg(distinct(artistname), ' & ') AS artist_primary_name, 
    string_agg(distinct(a1.artistalias), ' & ') AS performed_song_with_alias, 
    string_agg(a2.artistalias, ' & ') AS other_pseudonymes, 
    song 
FROM 
    artistalias a1 
    left JOIN artistalias a2 ON a2.artistname_id = a1.artistname_id 
    left JOIN songs s ON s.artistalias_id = a1.artistalias_id 
    left JOIN artistnames ON artistnames.artistname_id = a1.artistname_id 
GROUP BY song; 

显示other_pseudonymes柱像

+----------------------------+-----------------------------+--------------------------------------------------------------------+--------+ 
| artist_primary_name  | performed_song_with_alias | other_pseudonymes             | song | 
+----------------------------+-----------------------------+--------------------------------------------------------------------+--------+ 
| Francis Veigar    | Francis Veigar    | Francis Veigar & Francis Fat & Francis Fighter      | Song 1 | 
+----------------------------+-----------------------------+--------------------------------------------------------------------+--------+ 
| Francis Veigar    | Francis Fat     | Francis Veigar & Francis Fat & Francis Fighter      | Song 2 | 
+----------------------------+-----------------------------+--------------------------------------------------------------------+--------+ 
| Francis Veigar & Peeka Boo | Francis Fighter & Peeka Boo | Francis Veigar & Francis Fat & Francis Fighter & Peeka Boo & Peeka | Song 3 | 
+----------------------------+-----------------------------+--------------------------------------------------------------------+--------+ 
| Peeka Boo     | Peeka      | Peeka Boo & Peeka             | Song 4 | 
+----------------------------+-----------------------------+--------------------------------------------------------------------+--------+ 

我希望它看起来像

+----------------------------+-----------------------------+--------------------------------------------------------------------+--------+ 
| artist_primary_name  | performed_song_with_alias | other_pseudonymes             | song | 
+----------------------------+-----------------------------+--------------------------------------------------------------------+--------+ 
| Francis Veigar    | Francis Veigar    | Francis Veigar & Francis Fat & Francis Fighter      | Song 1 | 
+----------------------------+-----------------------------+--------------------------------------------------------------------+--------+ 
| Francis Veigar    | Francis Fat     | Francis Veigar & Francis Fat & Francis Fighter      | Song 2 | 
+----------------------------+-----------------------------+--------------------------------------------------------------------+--------+ 
| Francis Veigar & Peeka Boo | Francis Fighter & Peeka Boo | Francis Veigar & Francis Fat & Francis Fighter/Peeka Boo & Peeka | Song 3 | 
+----------------------------+-----------------------------+--------------------------------------------------------------------+--------+ 
| Peeka Boo     | Peeka      | Peeka Boo               | Song 4 | 
+----------------------------+-----------------------------+--------------------------------------------------------------------+--------+ 

分离pseudonymes /别名两种不同的艺术家正在使用'/'。在查询中需要改变什么才能实现这个目标？

Answer 1

使用子查询，然后SELECT聚集的元素，然后再次使用string_agg：

SELECT 
    string_agg(distinct(artistname), ' & ') AS artist_primary_name, 
    string_agg(distinct(a1.artistalias), ' & ') AS performed_song_with_alias, 
    string_agg(distinct(col),'/') AS other_pseudonymes, 
    song 
FROM 
    artistalias a1 
    left JOIN artistalias a2 ON a2.artistname_id = a1.artistname_id 
    left JOIN songs s ON s.artistalias_id = a1.artistalias_id 
    left JOIN artistnames ON artistnames.artistname_id = a1.artistname_id 
    left join 
(SELECT 
    string_agg(a2.artistalias, ' & ') as col, 
    artistname_id 
FROM artistalias a2 
GROUP BY artistname_id) 
AS aggregated_aliases ON aggregated_aliases.artistname_id = artistnames.artistname_id 
GROUP BY song;