ORDER BY 条件
我想检索有序查询结果，但我需要在列表前面有一些特定的行。就像 Stack Overflow 上的情况一样，在答案列表中，正确答案始终是第一个。
假设我需要将 ID 为 1,2,3 的行作为头，其余行按日期字段排序，是否可以执行以下操作：
SELECT * FROM foo ORDER BY id IN (1,2,3), created_date
如果不是，什么更有效？会有很多行！
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 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.
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
or
SELECT *, IF(id IN (1,2,3), 0, 1) AS head
ORDER BY head, created_date
(I use MySQL now, but I'm interested in any other SQL solution.)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
UNION 表示 UNION DISTINCT，这相对较慢检查是否有重复项，即使不会有任何重复项。您想要 UNION ALL:
我想在这次更改之后，三个查询之间的性能没有太大差异。最好的确定方法是测量它。
UNION means UNION DISTINCT and this is relatively slow as it will check for duplicates even though there will not be any. You want UNION ALL:
I would imagine that after this change there is not much difference in performance between the three queries. The best way to be sure is to measure it.
你的第一个例子对我来说几乎就在那里。
添加了
DESC，因为如果 true，id IN (1,2,3)返回1，如果 false，则返回0。 <代码>1> 0，因此按降序排列它们可以获得所需的结果。添加了
ASC因为我喜欢明确。根据您后面的示例，我认为您缺少的是，尽管它包含空格，但
field IN (list)是一个返回0或的单个运算符>1。IF(id IN (1,2,3), 0, 1)本质上是冗余的。因此，您不需要手动使用
UNION或 排序，因为 MySQL 使这比您想象的更简单:)Your first example looks almost there to me.
Added
DESCbecauseid IN (1,2,3)returns1if true or0if false.1 > 0, so ordering them in descending order gets the desired result.Added
ASCbecause I like to be explicit.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 returns0or1.IF(id IN (1,2,3), 0, 1)is inherently redundant.As such, you shouldn't need to use a
UNIONor sort manually, since MySQL makes this simpler than you even realize :)