ORDER BY条件 [英] ORDER BY condition

问题描述

我想检索一个有序的查询结果，但是我需要在列表的前面有一些特定的行.类似于堆栈溢出"中的内容，在答案列表中，正确的答案始终是第一个答案.

I would like to retrieve an ordered query result, but I need to have some specific row(s) to be in front of the list. Something like here on Stack Overflow, in the list of answers the right answer is always the first one.

假设我需要将ID为1,2,3的行作为标题，其余按日期字段排序，是否可以执行以下操作:

Assumed I need to have the rows with IDs 1,2,3 to be the head, the rest sorted by a date field, is it possible to do something like:

SELECT * FROM foo ORDER BY id IN (1,2,3), created_date

如果不是更有效的方法?将有很多行！

If not what is more efficient? There will be many rows!

SELECT *, 0 AS head FROM foo WHERE id IN (1,2,3) 
UNION
SELECT *, 1 AS head FROM foo WHERE id NOT IN (1,2,3)
ORDER BY head, created_date

或

SELECT *, IF(id IN (1,2,3), 0, 1) AS head
ORDER BY head, created_date

(我现在使用MySQL，但是我对其他任何SQL解决方案都感兴趣.)

(I use MySQL now, but I'm interested in any other SQL solution.)

推荐答案

您的第一个示例对我来说差不多.

Your first example looks almost there to me.

SELECT * FROM foo ORDER BY id IN (1,2,3) DESC, created_date ASC

添加了DESC，因为id IN (1,2,3)如果为true，则返回1；如果为false，则返回0. 1 > 0，因此按降序对其进行排序即可获得理想的结果.

Added DESC because id IN (1,2,3) returns 1 if true or 0 if false. 1 > 0, so ordering them in descending order gets the desired result.

添加了ASC，因为我喜欢明确表达.

Added ASC because I like to be explicit.

根据后面的示例，我认为您缺少的是尽管field IN (list)包含空格，但它是返回0或1的单个运算符. IF(id IN (1,2,3), 0, 1)本质上是多余的.

Based on your later examples, I think what you're missing is that, though it contains spaces, field IN (list) is a single operator that returns 0 or 1. IF(id IN (1,2,3), 0, 1) is inherently redundant.

因此，您不需要手动使用UNION 或进行排序，因为MySQL使此操作比您甚至意识到的还简单:)

As such, you shouldn't need to use a UNION or sort manually, since MySQL makes this simpler than you even realize :)

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