sql - 如何从 PostgreSQL 的子查询中选择包含值数组的列？

Question

给定 和 的假设模式Manufacturer，Model是否可以选择所有制造商，其列包含模型 ID 数组？这就像UNNEST的反面，但没有“嵌套”功能。

我试过了：

SELECT *, (SELECT md.id FROM model md WHERE md.parent_id = ma.id)
    FROM manufacturer ma;

但是得到了错误：

ERROR:  more than one row returned by a subquery used as an expression

标签： sqlarrayspostgresql

Answer 1

当我输入问题时，我意识到解决方案是我缺少ARRAY()构造函数：

SELECT *, ARRAY(SELECT md.id FROM model md WHERE md.parent_id = ma.id)
    FROM manufacturer ma;

来自文档：https ://www.postgresql.org/docs/11/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS

---

编辑：上面的方法虽然有效，但速度非常慢。更好的方法是使用以下方法连接表并聚合它们ARRAY_AGG()：

SELECT ma.*, ARRAY_AGG(md.id)
    FROM manufacturer ma
    JOIN model md ON md.parent_id = ma.id
    GROUP BY ma.id;

manufacturer.id必须是能够使用的主键ma.*，但您可以按任何内容进行分组。

在我的数据库中，这个版本需要 6 秒来检索 3k 行，而上面的方法需要 67 秒。