SQL排序顺序按查询中指定的顺序 [英] SQL Sort Order by The Order Specified In the Query

问题描述

说我有一个查询"select * from子句，其中id在(0,2,5,1,3)中"，我实际上希望返回的行以它们在where子句中指定的相同顺序返回. ID的顺序将随查询的不同而改变，并且该顺序没有模式.

Say I have a query "select * from clauses where id in (0,2,5,1,3)" and I actually want the rows returned in the same order they are specified the where clause. The order of the IDs will change from query to query and there is no pattern to the order.

我知道可以更改数据模型，创建临时表等等.但是请相信我，这些类型的解决方案在我的情况下将行不通.我也无法在应用程序代码中更改结果对象的顺序.

I know it's possible to alter the data model, create temp tables, etc. But trust me that those types of solutions will not work in my situation. I also am unable to alter the order of the result objects in my application code.

我也知道，不同的数据库引擎对事物的排序方式不同，在某些情况下无法保证等等.我只是不想，这可能吗?

I'm also aware that different database engines sort things differently, there are no guarantees in some situations blah blah blah. I just want to no, is this possible?

如果有帮助，我将使用mysql或sql服务器:)

I'll be using mysql or sql server if that helps :)

推荐答案

在MySQL上，您可以使用

On MySQL, you can use FIND_IN_SET:

ORDER BY FIND_IN_SET(id, '0,2,5,1,3')

最方便的订购方式是使用CASE表达式:

The most portable means of ordering would be to use a CASE expression:

ORDER BY CASE id
           WHEN 0 THEN 1
           WHEN 2 THEN 2
           WHEN 5 THEN 3
           WHEN 1 THEN 4
           WHEN 3 THEN 5
         END

这篇关于SQL排序顺序按查询中指定的顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！