將多對多連接的右側轉換為數組

在多對多關係上使用連接時，結果將拆分為多行。我想做的是將連接的右側轉換為數組，因此結果是一行。

以 3 個表為例：

CREATE TABLE items (
 id    serial primary key,
 title text
);

CREATE TABLE tags (
 id    serial primary key,
 title text
);

CREATE TABLE items_tags (
 item_id int references items(id),
 tag_id  int references tags(id),
 primary key (item_id, tag_id)
);

當選擇帶有標籤的項目時，我可以這樣做：

SELECT i.id, i.title, i.title
FROM items i
INNER JOIN items_tags it
ON it.item_id = i.id
INNER JOIN tags t
ON t.id = it.tag_id;

結果將如下所示：

(1, "item n1", "sport")
(1, "item n1", "soccer")
(2, "item n2", "adventure")
(2, "item n2", "mountain climbing")
(2, "item n2", "sport")
(2, "item n2", "nature")

我想要的是這樣的：

(1, "item n1", ["sport", "soccer"])
(2, "item n2", ["adventure", "mountain climbing", "sport" , "nature"])

聚合大多數行

在查詢所有或大多數項目時，首先從“許多”表聚合行並稍後連接通常要快得多：

SELECT id, i.title AS item_title, t.tag_array
FROM   items      i
JOIN  (  -- or LEFT JOIN ?
  SELECT it.item_id AS id, array_agg(t.title) AS tag_array
  FROM   items_tags it
  JOIN   tags       t  ON t.id = it.tag_id
  GROUP  BY it.item_id
  ) t USING (id);

LEFT [OUTER] JOIN如果可以有沒有標籤的項目，則在外部查詢中使用- 這將被排除在[INNER] JOIN.

由於這不會增加連接中的行，因此我們不需要GROUP BY在外部SELECT.

在聚合之前加入也會在列表中出現多個 1:n 表時失控FROM（在這種簡單的情況下不是這樣）。看：

• 兩個 SQL LEFT JOINS 產生不正確的結果

聚合幾行

對於一小部分行，使用LATERAL帶有ARRAY 建構子的連接：

SELECT id, title AS item_title, t.tag_array
FROM   items i, LATERAL (  -- this is an implicit CROSS JOIN
  SELECT ARRAY (
     SELECT t.title
     FROM   items_tags it
     JOIN   tags       t  ON t.id = it.tag_id
     WHERE  it.item_id = i.id
     ) AS tag_array
  ) t;

由於 ARRAY 建構子總是產生一行（如果子查詢為空，則為空數組 - 結果的細微差別！），LEFT JOIN LATERAL (...) ON true這裡不需要。看：

• LATERAL 和 PostgreSQL 中的子查詢有什麼區別？
• 為什麼 array_agg() 比非聚合 ARRAY() 建構子慢？
• 了解聚合的多表連接

在旁邊

您的查詢中有錯字。第 3 列將是t.title. 我為您的原始（未聚合）查詢添加了別名以澄清：

SELECT i.id, i.title AS item_title, t.title AS tag_title
FROM   items      i
JOIN   items_tags it ON it.item_id = i.id
JOIN   tags       t  ON t.id = it.tag_id;

“id”或“title”通常不是很獨特且不是很有用的標識符。看：

• 如何在 PostgreSQL 中實現多對多關係？

引用自：https://dba.stackexchange.com/questions/173831