我有一个问题,创建按以下逻辑排序的选择查询:
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 |
+---------+-------+-------+
任何想法?
MySQL中比较的结果是0
或1
。如果是true
,那么Stock > 0
就是1
。1
和大于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