这个问题是我之前的问题的延续,可以在这里找到。
这个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)可以有0个,一个或多个别名
- 一个别名(表artistalias)只属于一个艺术家
- 一首歌曲(表格歌曲)有一个或多个艺术家(指他用来演唱这首歌的艺术家的别名)
的例子:艺术家Francis Veigar也使用别名Francis Fat和Francis Fighter。其中一首歌曲1以艺人名Francis Veigar发行,另一首歌曲2使用笔名Francis Fat,第三首歌曲3使用别名Francis Fighter,与另一位名为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 |
+----------------------------+-----------------------------+--------------------------------------------------------------------+--------+
用'/'分隔两个不同艺术家使用的假名/别名。要实现这一点,查询中需要更改什么?
使用子查询,然后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;