按顺序按子句使用布尔表达式 [英] Using boolean expression in order by clause

问题描述

我有一个如下的order by子句:

( user_id <> ? ), rating DESC, title

在哪里?替换为当前用户的ID.

在postgresql上，这为我提供了要查找的顺序，即当前用户，最高评分，标题(按字母顺序).

但是在MySQL上，我的命令不明确，当前用户既不是第一也不是最后一个，也不是按等级或职称排名.

我是跨数据库兼容性的唯一选择，是在.. THEN .. ELSE .. END语句时用CASE替换此快速且肮脏的布尔表达式吗?

谢谢大家的帮助，Chaos和Chad Birch正确指出了问题出在其他地方的情况(特别是我将上面查询的结果用作下一个查询的输入) -然后惊讶地发现第一个顺序丢失了；)

解决方案

我在mysql中对此进行了一些测试，它们都正常运行(您所期望的方式).我想您的问题必须是查询之外的其他地方.为了验证自己，我建议直接从mysql客户端运行等效查询.

I have an order by clause that looks like:

( user_id <> ? ), rating DESC, title

Where ? is replaced with the current user's id.

On postgresql this gives me the ordering I'm looking for i.e. by current user, then highest rating, then title (alphabetically).

However on MySQL I get an unclear order current user is neither first nor last, nor is it by rating or title.

Is my only option for cross database compatibility to replace this quick and dirty boolean expression with a CASE WHEN .. THEN .. ELSE .. END statement?

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

解决方案

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.

这篇关于按顺序按子句使用布尔表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！