PostgreSQL：按数组元素关联查询并保留组装顺序

要保留assembly_order数组中的零件顺序，你需要在展开数组时同时获取元素的位置索引，再通过这个索引排序。修改后的SQL语句如下：

SELECT p.id, p.serial_no
FROM process.assemblies a
CROSS JOIN UNNEST(a.assembly_order) WITH ORDINALITY AS parts(id, position)
JOIN process.parts p ON p.id = parts.id
WHERE a.id = 1
ORDER BY parts.position;

关键说明 #

• UNNEST(...) WITH ORDINALITY：这个语法在展开数组时，会额外生成一列position，记录每个元素在原数组中的位置（从1开始计数）。
• 通过CROSS JOIN关联组装件表和展开后的结果，确保只获取目标组装件对应的零件。
• 最后用ORDER BY parts.position按照原数组的顺序排序，保证查询结果的顺序和assembly_order完全一致。

原语句失效原因 #

原语句中的IN子查询会将unnest输出的零件ID视为无序集合，数据库会按照自身优化逻辑（比如主键顺序）返回结果，不会遵循原数组的排列顺序。

内容的提问来源于stack exchange，提问作者tomsrobots