基于搜索参数返回数据行功能正常，但结果顺序不符如何解决？

问题描述 #

从my_table表中根据数组参数返回数据时，查询功能正常，但返回结果的顺序和数组中参数的顺序不匹配。

执行的SQL语句：

select * from my_table where internal_id = any(array[
  'one@gmail.co.uk',
  'two@gmail.co.uk',
  'three@gmail.co.uk'
]);

返回结果：

解决方案 #

PostgreSQL中使用= ANY()查询时，结果默认按表的存储顺序或索引顺序返回，不会遵循数组参数的顺序。要让结果与数组顺序一致，可采用以下两种方法：

方法1：用array_position指定排序依据 #

在查询末尾添加ORDER BY子句，借助array_position函数获取每个internal_id在目标数组中的位置，按该位置排序即可匹配数组顺序：

SELECT *
FROM my_table
WHERE internal_id = ANY(ARRAY[
  'one@gmail.co.uk',
  'two@gmail.co.uk',
  'three@gmail.co.uk'
])
ORDER BY array_position(ARRAY[
  'one@gmail.co.uk',
  'two@gmail.co.uk',
  'three@gmail.co.uk'
], internal_id);

方法2：通过UNNEST WITH ORDINALITY关联排序 #

如果数组元素较多，为避免重复书写数组，可先拆分数组并带上原始序号，再关联原表后按序号排序：

SELECT t.*
FROM my_table t
JOIN UNNEST(ARRAY[
  'one@gmail.co.uk',
  'two@gmail.co.uk',
  'three@gmail.co.uk'
]) WITH ORDINALITY arr(id, ord) ON t.internal_id = arr.id
ORDER BY arr.ord;

以上两种方法都能让查询结果严格按照数组参数的顺序返回。

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