混合字符串和数值排序 - Mixed string and numeric value ordering

Mixed string and numeric value ordering

本文关键字：排序字符串混合 | 更新日期: 2023-09-27

$query = "SELECT * FROM price   
ORDER BY CAST(price AS DECIMAL(10,2))  
DESC LIMIT $from, $max_results";

此查询显示的结果如下所示

Example
________________________
Product      | Price
________________________
Shoes         | 94,200 
________________________
Shirts        |66,900
________________________
Socks         |59,900
________________________
T-shirt       |49,700
________________________
Shirt Cloth   |Coming Soon
________________________
Shirt pant    |Coming Soon
________________________
Shirt jacket  |Coming Soon
________________________

我想要那样

Example
________________________
Product      | Price
________________________
Shirt Cloth   |Coming Soon
________________________
Shirt pant    |Coming Soon
________________________
Shirt jacket  |Coming Soon
________________________
Shoes         | 94,200 
________________________
Shirts        |66,900
________________________
Socks         |59,900
________________________
T-shirt       |49,700
________________________

我

该怎么做，请帮我解决这个问题谢谢

你可以通过以下方式执行条件顺序

ORDER BY
  CASE price
    WHEN "Coming Soon" THEN 1
    ELSE 2
  END ASC,
CAST(REPLACE(price, ',', '') AS decimal(10,2)) DESC

这样做的美妙之处在于你可以有多种条件......因此，假设除了"即将推出"之外，您还有另一个值和一个数字。像"缺货"这样的东西，您可以轻松地将其合并到您的声明中

order by可以有任意表达式：

ORDER BY (price = 'Coming Soon') DESC, CAST(price AS decimal(10,2))

如果 price 为 Coming Soon ，则price = 'Coming Soon'的计算结果为 TRUE。如果它不相等，那么它是布尔假的。通过DESC排序，真值将排在第一位，然后是假值。在这些真/假块中，CAST(...)将进一步对实际价格进行排序。