postgresql 在 order by 子句中使用布尔表达式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/591608/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using boolean expression in order by clause
提问by waldo
I have an order by clause that looks like:
我有一个 order by 子句，如下所示：
( user_id <> ? ), rating DESC, title
Where ? is replaced with the current user's id.
在哪里 ？替换为当前用户的 ID。
On postgresql this gives me the ordering I'm looking for i.e. by current user, then highest rating, then title (alphabetically).
在 postgresql 上，这给了我我正在寻找的排序，即按当前用户排序，然后是最高评级，然后是标题（按字母顺序）。
However on MySQL I get an unclear order current user is neither first nor last, nor is it by rating or title.
然而，在 MySQL 上，我得到一个不清楚的顺序，当前用户既不是第一个也不是最后一个，也不是按评级或标题。
Is my only option for cross database compatibility to replace this quick and dirty boolean expression with a CASE WHEN .. THEN .. ELSE .. END statement?
我唯一的跨数据库兼容性选择是用 CASE WHEN .. THEN .. ELSE .. END 语句替换这个快速而肮脏的布尔表达式吗？
Edit: Thanks all for the assistance, it is as correctly pointed out by Chaos and Chad Birch the case that the problem lies elsewhere (specifically that I'm using the results of the above query as input into the next - then acting surprised that the order of the first is lost ;)
编辑：感谢所有人的帮助，正如 Chaos 和 Chad Birch 正确指出的那样，问题出在其他地方（特别是我将上述查询的结果用作下一个查询的输入 - 然后表现出惊讶的是第一个的顺序丢失了;)
采纳答案by chaos
I tested several variations on this in mysql and they all worked correctly (the way you're expecting). I suppose your problem has to be somewhere other than the query. To verify for yourself, I suggest running an equivalent query directly from mysql client.
我在 mysql 中测试了几个变体，它们都正常工作（正如你所期望的那样）。我想你的问题必须在查询以外的地方。为了自己验证，我建议直接从 mysql 客户端运行等效的查询。
回答by Alex Barrett
MySQL has no realnotion of booleans, and simply maps TRUEand FALSEto the numeric values 1and 0repectively.
MySQL有没有真正的布尔值的概念，简单地映射TRUE，并FALSE在数值1和0repectively。
In this case user_id <> ?will return 0 for the majority of the rows in your table and 1 for the other rows. The default sort order is ASC, meaning in all likelihood the rows you want are at the bottomof your result set (0/FALSEcome before1/TRUE). Try modifying your query to accommodate this.
在这种情况下，user_id <> ?将为表中的大多数行返回 0，为其他行返回 1。默认排序顺序是ASC，这意味着您想要的行很可能位于结果集的底部（0/FALSE在之前1/TRUE）。尝试修改您的查询以适应此情况。
( user_id <> ? ) DESC, rating DESC, title
Assuming this is indeed the issue, cross-database compatibility can be achieved with ease.
假设这确实是问题所在，则可以轻松实现跨数据库兼容性。
IF(user = ?, 0, 1), rating DESC, title
回答by Ed Greaves
You could try doing a
你可以尝试做一个
select (user_id <> ?), user_id
to see that you are getting the right true/false values showing up.
看看你是否得到了正确的真/假值。
