如何将关联的Beat表与Tag表多行查询结果合并为单行？

表结构 #

BEAT表 #

CREATE TABLE beat (
  id BIGSERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  artist_name TEXT NOT NULL,
  genre TEXT
);

TAG表 #

CREATE TABLE tag (
  id BIGSERIAL PRIMARY KEY,
  beat_id BIGINT REFERENCES beat (id) NOT NULL,
  tag_name TEXT NOT NULL
);

一条Beat记录可对应多条Tag记录，例如某条Beat关联2个Tag。当前使用以下查询语句：

SELECT
  *
FROM
  beat
  LEFT JOIN tag ON tag.beat_id = beat.id
WHERE beat.id = 1;

该查询会返回两行结果，每行重复Beat信息并附带一条Tag信息。需求是将结果合并为单行，同时包含该Beat的所有Tag信息。

示例数据 #

INSERT INTO beat (title, artist_name, genre) VALUES ('Beat-1', 'Artist-1', 'electro');

INSERT INTO tag (beat_id, tag_name) VALUES (1, 'Tag-1');

INSERT INTO tag (beat_id, tag_name) VALUES (1, 'Tag-2');

解决方案 #

聚合为单行（推荐） #

利用PostgreSQL的STRING_AGG函数，将同一Beat下的所有Tag名称合并为一个字符串，实现单行返回：

SELECT
  beat.id,
  beat.title,
  beat.artist_name,
  beat.genre,
  STRING_AGG(tag.tag_name, ', ') AS tags
FROM
  beat
LEFT JOIN tag ON tag.beat_id = beat.id
WHERE beat.id = 1
GROUP BY beat.id, beat.title, beat.artist_name, beat.genre;

执行结果

针对示例数据，查询返回单行结果：

id |  title  | artist_name |  genre  |    tags
----+---------+-------------+---------+-------------
  1 | Beat-1  | Artist-1    | electro | Tag-1, Tag-2

按实体拆分展示（匹配预期输出格式） #

如果需要先展示Beat信息，再逐条列出关联的Tag信息，可使用UNION ALL组合查询：

SELECT 'beat' AS entity_type, id::TEXT, title, artist_name, genre, NULL AS beat_id, NULL AS tag_name
FROM beat WHERE id = 1
UNION ALL
SELECT 'tag' AS entity_type, id::TEXT, NULL, NULL, NULL, beat_id::TEXT, tag_name
FROM tag WHERE beat_id = 1
ORDER BY entity_type;

执行结果

entity_type | id |  title  | artist_name |  genre  | beat_id | tag_name
-------------+----+---------+-------------+---------+---------+----------
beat         | 1  | Beat-1  | Artist-1    | electro |         |
tag          | 1  |         |             |         | 1       | Tag-1
tag          | 2  |         |             |         | 1       | Tag-2

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