问题描述
我有一张这样的桌子
id title display_order
1 t1 3
2 t2 1
3 t3 5
4 t4 4
5 t5 2
6 t6 0
7 t7 7
8 t8 6
9 t9 0
10 t10 0
我需要的是这样的结果
id title display_order
2 t2 1
5 t5 2
1 t1 3
4 t4 4
3 t3 5
8 t8 6
7 t7 7
...order of the rest is not important but should be in the result
6 t6 0
9 t9 0
10 t10 0
我可以通过两个SQL查询获得此结果，然后将它们组合起来.
I can get this result with two SQL queries and then combine them.
有没有办法用一个SQL做到这一点?
Is there a way to do this with one SQL?
谢谢
推荐答案
SELECT *
FROM atable
ORDER BY
display_order = 0,
display_order
当display_order为0时，第一个排序项display_order = 0的计算结果为True，否则它的计算结果为False. True在False之后排序–因此，第一个排序标准确保display_order为0的行在列表的末尾排序.
When display_order is 0, the first sorting term, display_order = 0, evaluates to True, otherwise it evaluates to False. True sorts after False – so, the first sorting criterion makes sure that rows with the display_order of 0 are sorted at the end of the list.
第二个ORDER BY术语display_order另外指定了具有非零阶值的行的阶.
The second ORDER BY term, display_order, additionally specifies the order for rows with the non-zero order values.
因此，这两个条件为您提供了所需的排序顺序.
Thus, the two criteria give you the desired sorting order.
这篇关于获取和排序值大于零的行，然后获取值零的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！