IN() 子句中的 PostgreSQL ORDER BY 值 [英] PostgreSQL ORDER BY values in IN() clause

问题描述

好的，有一些关于如何做到这一点的答案.但是所有的答案都假设查询是全选的.如果您有不同的选择，则这些方法不再有效.

Ok, there are some answers out there on how to do this. But all of the answers are assuming that the query is selecting all. If you have a distinct select, the methods no longer work.

请参阅此处了解该方法:在 Postgresql 中模拟 MySQL 的 ORDER BY FIELD()

See here for that method: Simulating MySQL's ORDER BY FIELD() in Postgresql

基本上我有

SELECT DISTINCT id 
FROM items 
WHERE id IN (5,2,9) 
ORDER BY
 CASE id
  WHEN 5 THEN 1 
  WHEN 2 THEN 2
  WHEN 9 THEN 3
 END

当然，这会打破并说

"PGError: ERROR: 对于 SELECT DISTINCT, ORDER BY 表达式必须出现在选择列表中"

"PGError: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list"

有什么办法可以按照 IN 子句中值的顺序对 PostgreSQL 中的查询结果进行排序吗?

Is there any way to order your query results in PostgreSQL by the order of the values in the IN clause?

推荐答案

您可以将其包装到派生表中:

You can wrap it into a derived table:

SELECT *
FROM (
  SELECT DISTINCT id 
  FROM items 
  WHERE id IN (5,2,9) 
) t
ORDER BY
 CASE id
  WHEN 5 THEN 1 
  WHEN 2 THEN 2
  WHEN 9 THEN 3
 END

这篇关于IN() 子句中的 PostgreSQL ORDER BY 值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！