在IN值清单中排序 [英] ORDER BY the IN value list

问题描述

我在PostgreSQL 8.3中有一个简单的SQL查询，可以获取大量注释.我在WHERE子句中的IN构造中提供了 sorted 值列表:

I have a simple SQL query in PostgreSQL 8.3 that grabs a bunch of comments. I provide a sorted list of values to the IN construct in the WHERE clause:

SELECT * FROM comments WHERE (comments.id IN (1,3,2,4));

这会以任意顺序返回评论，而在我看来，它们恰好是1,2,3,4之类的ID.

This returns comments in an arbitrary order which in my happens to be ids like 1,2,3,4.

我希望结果行像IN构造中的列表一样排序:(1,3,2,4).
如何实现?

I want the resulting rows sorted like the list in the IN construct: (1,3,2,4).
How to achieve that?

推荐答案

您可以很容易地使用PostgreSQL(在PostgreSQL 8.2中引入)VALUES()，().

You can do it quite easily with (introduced in PostgreSQL 8.2) VALUES (), ().

语法如下:

select c.*
from comments c
join (
  values
    (1,1),
    (3,2),
    (2,3),
    (4,4)
) as x (id, ordering) on c.id = x.id
order by x.ordering

这篇关于在IN值清单中排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！