具有多个值的数组列上的LEFT OUTER JOIN

&&

没错，重叠运算符

&&

SELECt * FROM eg_assoc WHERe actors && '{1}'::int[]

但是 ，查询的逻辑是相反的，查找中的数组中列出的所有人员

eg_assoc

person.id

这不是一个小问题。首先阅读以下内容：

• PostgreSQL unnest（），元素编号

以下查询将 完全按照给定的 顺序保留原始数组，包括可能重复的元素和元素的原始顺序。适用于 一维数组
。其他尺寸将折叠为一个尺寸。保留多个维度较为复杂（但完全可能）：

对于Postgres 8.4+
（已在何处

generate_subsrcipts()

SELECt aid, actors     , ARRAY( SELECT name   FROM   generate_subscripts(e.actors, 1) i   JOIN   eg_person p ON p.id = e.actors[i]   ORDER  BY i) AS act_names     , benefactors     , ARRAY( SELECt name   FROM   generate_subscripts(e.benefactors, 1) i   JOIN   eg_person p ON p.id = e.benefactors[i]   ORDER  BY i) AS ben_namesFROM   eg_assoc e;

即使在第9.3页中，仍可能表现最佳。
使用比快的数组构造函数

array_agg()

LATERAL

对于PostgreSQL 9.3+ 。

SELECt e.aid, e.actors, a.act_names, e.benefactors, b.ben_namesFROM   eg_assoc e, LATERAL (   SELECt ARRAY( SELECT name      FROM   generate_subscripts(e.actors, 1) i      JOIN   eg_person p ON p.id = e.actors[i]      ORDER  BY i)   ) a(act_names), LATERAL (   SELECt ARRAY( SELECT name      FROM   generate_subscripts(e.benefactors, 1) i      JOIN   eg_person p ON p.id = e.benefactors[i]      ORDER  BY i)   ) b(ben_names);

SQL Fiddle 具有两个变体（第pg 9.4版除外）。

微妙的细节：如果找不到人，则将其遗漏。如果找不到整个数组，则这两个查询都会生成一个 空数组 （

'{}'

NULL

WITH ORDINALITY

SELECt aid, actors     , ARRAY(SELECT name  FROM   unnest(e.actors) WITH ORDINALITY a(id, i)  JOIN   eg_person p USING (id)  ORDER  BY a.i) AS act_names     , benefactors     , ARRAY(SELECt name  FROM   unnest(e.benefactors) WITH ORDINALITY b(id, i)  JOIN   eg_person USING (id)  ORDER  BY b.i) AS ben_namesFROM   eg_assoc e;

@a_horse提供
的查询 似乎
可以完成任务，但是它不可靠，具有误导性，可能不正确且不必要地昂贵。

1. 代理交叉联接，因为有两个不相关的联接。偷偷摸摸的反模式。细节：

   • 两个SQL LEFT JOINS产生不正确的结果

使用

DISTINCT

array_agg()

1. 该表达式

   a_person.id = any(eg_assoc.actors)

   有效 ，但会从结果中 消除重复项 （在此查询中发生两次），除非指定，否则是错误的。

2. 不保留数组元素的 原始 顺序 。一般来说，这很棘手。但这在查询中会加剧，因为参与者和恩人被相乘并再次变得不同，从而 保证了 任意顺序。

3. 外部没有列别名会

   SELECT

   导致重复的列名，这会使某些客户端失败（在没有别名的小提琴中无法使用）。

4. min(actors)

   而且

   min(benefactors)

   没有用。通常情况下，只需将列添加到其中，

   GROUP BY

   而不用假汇总它们。但是

   eg_assoc.aid

   无论如何PK列（在中包含整个表

   GROUP BY

   ），所以甚至没有必要。只是

   actors, benefactors

   。

汇总整个结果会浪费时间和精力。使用更智能的查询，而不是将基本行相乘，则不必将它们汇总在一起。