获取和排序值大于零的行，然后获取值零的行 [英] Getting and ordering rows with value greater than zero then rows with value zero

问题描述

我有一张这样的桌子

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.

这篇关于获取和排序值大于零的行，然后获取值零的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！