PostgreSQL中如何按传入数组顺序返回匹配的main_id查询结果

要实现按传入参数的顺序返回结果，核心是在解析数组时保留元素的原始位置，再基于该位置排序。以下是两种可行的修改方案：

通用版本（兼容所有支持WITH ORDINALITY的PostgreSQL版本） #

SELECT t.main_id
FROM test t
JOIN (
    SELECT 
        f.id AS first_id, 
        s.id AS second_id, 
        f.ord AS position
    FROM UNNEST(@first_ids::uuid[]) WITH ORDINALITY f(id, ord)
    JOIN UNNEST(@second_ids::uuid[]) WITH ORDINALITY s(id, ord)
    ON f.ord = s.ord
) p ON (t.first_id, t.second_id) = (p.first_id, p.second_id)
ORDER BY p.position;

核心逻辑说明： #

• WITH ORDINALITY会给UNNEST展开的每个数组元素附加一个ord字段，代表该元素在原数组中的位置（从1开始计数）。
• 通过ON f.ord = s.ord确保first_ids和second_ids中的元素严格按传入时的对应位置配对，避免交叉关联。
• 最终通过ORDER BY p.position强制结果集按照传入参数的顺序返回。

简化版本（PostgreSQL 10+适用） #

如果你的PostgreSQL版本在10及以上，可以直接同时展开两个数组，写法更简洁：

SELECT t.main_id
FROM test t
JOIN UNNEST(@first_ids::uuid[], @second_ids::uuid[]) WITH ORDINALITY p(first_id, second_id, ord)
ON (t.first_id, t.second_id) = (p.first_id, p.second_id)
ORDER BY p.ord;

该写法直接将两个数组按位置配对展开，同样通过ord字段控制排序顺序。

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