MySQL ORDER BY 两个字段条件 [英] MySQL ORDER BY two fields condition

问题描述

我在创建按以下逻辑排序的选择查询时遇到问题:

I have a problem with creating select query ordered by following logic:

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，表格将按价格排序.

The desired result is, that the table will be ordered by price if the stock value is greater than 0.

所以结果应该是:

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

有什么想法吗?

推荐答案

在 MySQL 中比较的结果是 0 或 1.所以 Stock >0 是 1 如果 true.并且 1 大于 0.所以要么使用

The result of a comparison in MySQL is 0 or 1. So Stock > 0 is 1 if true. And 1 and greather than 0. So either use

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

这篇关于MySQL ORDER BY 两个字段条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！