ORDER BY IN 值列表 [英] ORDER BY the IN value list

问题描述

我在 PostgreSQL 8.3 中有一个简单的 SQL 查询，它抓取了一堆注释.我为 WHERE 子句中的 IN 构造提供了一个 排序 值列表:

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 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

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