时间戳

首页 > 解决方案 > 在 PostgreSQL 中：如何将包含 FK 的数组字段转换为引用表中的名称数组？

arrays - 在 PostgreSQL 中：如何将包含 FK 的数组字段转换为引用表中的名称数组？

问题描述

我的数据库有一个产品表，如下所示：

PRODUCTS
----------------------------
 id | name      | suppliers
----------------------------
  1 | widget    | {1,2}
  2 | gizmo     | {1}
  3 | geegaw    | {3}
  4 | tchotchke | null

该列包含属于供应商表的 IDsuppliers数组 ( )：numeric[]

SUPPLIERS
------------
 id | name 
------------
  1 | alpha
  2 | beta
  3 | gamma

如何编写一个查询，该查询将返回PRODUCTS带有供应商名称数组而不是供应商 ID 号的 except 的内容？结果应如下所示：

-----------------------------------
 id | name      | suppliers
-----------------------------------
  1 | widget    | {'alpha','beta'}
  2 | gizmo     | {'alpha'}
  3 | geegaw    | {'gamma'}
  4 | tchotchke | null

简洁有效的方法将是首选，但可读性/可理解性也很好。

编辑：这不是链接问题的副本，尽管该问题确实涉及unnest操作，但它不会重新汇总结果。这个问题的答案对网站做出了新的贡献。

标签： arrayspostgresql

解决方案

select t1.id, t1.name, array_agg(s.Name) as Suppliers 
from Products t1 
left join lateral (
select unnest(suppliers) as supplierId from myProducts t2 
where t1.id = t2.id) as t on true
left join Suppliers s on s.Id = t.supplierID
group by t1.id, t1.name;

我在想什么。这是一个更好的版本：

select p.id, p.name, array_agg(s.Name) as Suppliers
from (select *,unnest(suppliers) as supplierId from Products) p 
left join Suppliers s on s.Id = p.supplierID
group by p.id, p.name;

推荐阅读