按WHERE子句中的项排序查询结果

Question

如何让查询结果按WHERE子句中传递的项目的确切顺序排序?

例如，使用以下查询:

SELECT id, name FROM my_table
WHERE id in (1,3,5,2,4,6)
ORDER BY id

结果:

id | name
---------
1  |  a
2  |  b
3  |  c
4  |  d
5  |  e
6  |  f

我所期望的:

id | name
---------
1  |  a
3  |  c
5  |  e
2  |  b
4  |  d
6  |  f

我注意到MySQL中有一个FIELD()函数。在PostgreSQL中是否有相应的功能?

Answer 1

传递数组并使用WITH ORDINALITY。这是最干净和最快的:

SELECT id, t.name
FROM   unnest ('{1,3,5,2,4,6}'::int[]) WITH ORDINALITY u(id, ord)
JOIN   my_table t USING (id)
ORDER  BY u.ord;

假设传递的数组中的值是不同的。否则，此解决方案保留重复项，而IN则删除它们。你必须定义你想要的行为。但是，期望的排序顺序也是不明确的，这将使问题没有意义。

:

• 按IN值列表排序
PostgreSQL unnest() with element number

Answer 2

@chris Kao，在postgresql中使用Position

方法:1SELECT id, name FROM my_table(1,3,5,2,4,6)按位置排序(id::text in '1,3,5,2,4,6')输出:

id|name|
--+----+
1|a   |
3|c   |
5|e   |
2|b   |
4|d   |
6|f   |

方法:2

select id, name
from my_table mt
where id in (1,3,5,2,4,6)
order by array_position(array[1,3,5,2,4,6], mt.id);