双域条件下的MySQL排序

Question

我在创建按以下逻辑排序的select查询时遇到了问题：

SELECT * FROM Products WHERE 1 ORDER BY Stock > 0, Price DESC

表样：

+---------+-------+-------+
| Product | Price | Stock |
+---------+-------+-------+
| Car     |  3500 |    30 |
| Boat    |  7500 |     6 |
| Bike    |   150 |   220 |
| Plane   | 55000 |     0 |
+---------+-------+-------+

期望的结果是，如果股票价值大于0，表将按价格排序。

因此，结果应该是：

+---------+-------+-------+
| Product | Price | Stock |
+---------+-------+-------+
| Boat    |  7500 |     6 |
| Car     |  3500 |    30 |
| Bike    |   150 |   220 |
| Plane   | 55000 |     0 |
+---------+-------+-------+

有什么想法吗？

Answer 1

MySQL中比较的结果是0或1。所以Stock > 0是1，如果是true。还有1和greather 0。所以要么用

ORDER BY Stock = 0 ASC, Price DESC

或

ORDER BY Stock > 0 DESC, Price DESC

或

ORDER BY case when Stock > 0
              then 1
              else 2
         end, 
         Price DESC