如何连接三个表并从第三个表聚合一列

Question

我有三个表，如下所示：

表A:

id
--
1
2

表B:

id   | table_a
-----+------------
100  | 1
101  | 1
102  | 2
103  | 2

表C:

id   | table_b | item
-----+---------+-------
200  | 100     | Apple
201  | 100     | Apple
202  | 101     | Orange
203  | 102     | Apple
204  | 103     | Banana
205  | 103     | Rice

我想为表A中的每一行获取一行，并聚合表C的内容。(我猜是使用ARRAY_AGG()函数吗？(
显然，表A与表B连接，后者与表C连接。最终表格将包含以下内容：

id_table_a | item
-----------+---------------------
1          | Apple, Orange
2          | Apple, Banana, Rice

Answer 1

您没有指定，但您的示例也建议：

• 。。。每个id_table_a消除重复项
• 。。。按c.id对项目进行排序-出于实际原因，我认为每组重复项的c.id最低

假设引用完整性，我们根本不需要涉及表a。更便宜。

要在按每组最小id排序时删除重复项，请使用两步聚合：

SELECT id_table_a
, string_agg(c.item, ', ' ORDER BY c_id) AS items
FROM  (
SELECT b.table_a AS id_table_a, c.item, min(c.id) AS c_id
FROM   b
JOIN   c ON c.table_b = b.id
GROUP  BY b.table_a, c.item
) c
GROUP  BY id_table_a
ORDER  BY id_table_a;

小提琴

通常在子查询中排序一次比在上面的查询中按组排序更快：

SELECT id_table_a
, string_agg(c.item, ', ') AS items
FROM  (
SELECT b.table_a AS id_table_a, c.item
FROM   b
JOIN   c ON c.table_b = b.id
GROUP  BY b.table_a, c.item
ORDER  BY b.table_a, min(c.id)
) c
GROUP  BY id_table_a
ORDER  BY id_table_a;

手册：

或者，提供排序子查询的输入值通常会起作用。