Postgres子查询，按子查询排序 [英] Postgres subquery, ordering by subquery

问题描述

如果我有一个查询，例如：

If I have a query such as :

select * from tbl where id in (10, 20, 9, 4);

返回的结果可能按以下顺序排列：
4，
9 ，
10，
20

the results returned could potentially be in this order: 4, 9, 10, 20

但是如果要维护传递到初始查询中的列表的顺序怎么办？您将如何处理？

but what if was looking to maintain the order of the list passed into the initial query? how would you approach this?

最终，我在这里使用Django作为我的应用程序的ORM，但我希望首先研究在数据库级别上可行的方法。

Ultimately I'm using Django as the ORM for my app here but I'm looking to investigate what is feasible at db level first.

最欢迎任何想法！

推荐答案

使用CASE不太方便，因为您必须知道提前所有值和要排序的顺序。最干净的方法是使用数组作为值，并按数组中项目的索引排序。

Using CASE isn't very portable because you have to know ahead of time all of the values and the order that you want to sort. The cleanest way is to use an array for the values and sort by the item's index in the array.

我已将其发布到Postgres 片段库前一阵子。

I had posted this to the Postgres Snippet Library a while back.

CREATE OR REPLACE FUNCTION idx(anyarray, anyelement)
  RETURNS int AS 
$$
  SELECT i FROM (
     SELECT generate_series(array_lower($1,1),array_upper($1,1))
  ) g(i)
  WHERE $1[i] = $2
  LIMIT 1;
$$ LANGUAGE sql IMMUTABLE;

SELECT *
FROM foo
ORDER BY idx（array ['Freshman' ，大二，初级，高级]，foo.grade_level）

SELECT * FROM foo ORDER BY idx(array['Freshman','Sophomore','Junior','Senior'], foo.grade_level)

这篇关于Postgres子查询，按子查询排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！