SQL查询按自定义顺序排序 [英] SQL query ordering in custom order

问题描述

我有这样的自定义订购需求:

I have a custom ordering need like this:

normal ordering  |  custom ordering 
      1          |        7
      2          |        6
      3          |        5
      4          |        4
      5          |        3
      6          |        2
      7          |        8
      .          |        .
      .          |        .
      .          |        .
      .          |        .
      .          |        N
      N          |        1

我曾考虑过使用UNION在ORDER BY和LIMIT的帮助下结合3个不同的选择查询.但是，我不能这样做，因为必须在之前 ORDER BY和LIMIT之前使用UNION.

I have thought about using UNION to combine 3 different select queries with the help of ORDER BY and LIMIT. However, I can not do that because UNION have to be used before ORDER BY and LIMIT.

如何进行选择(或多个选择)以实现上面的自定义排序?

How can I make a selection (or selections) to achieve the custom ordering above?

另一种解决方法可能会有所帮助，只是使此选择查询中返回的第一个记录为最后一个记录，但是如何?

Another workaround might help is just make the 1st record returned in this select query the last record, but how?

推荐答案

尝试一下:

SELECT x
FROM t1
ORDER BY
    CASE
      WHEN x = 1 THEN 100000001
      WHEN x between 2 and 7 THEN 7 - x
      WHEN x between 8 and ( SELECT max(x) FROM t1 ) - 1 THEN x
      ELSE 100000000 
    END 

100000000常数必须大于N.
这是一个简单演示

100000000 constans must be greather than N.
Here is a simple demo

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