ORDER BY分别为正数& MySQL语句中的负数 [英] ORDER BY separately positive & negative numbers in MySQL statement

问题描述

我有一个MySQL表，并希望按以下顺序基于列值返回行:

I have a MySQL table and would like to return the rows based on a column value in this order:

• 如果列> = 0则第一个升序
• 如果列< 0
，则降序排列

例如0,2,4,7，-2，-3，-5

E.g. 0,2,4,7,-2,-3,-5

推荐答案

可以使用签名将正数排序到顶部，然后使用

Can use SIGN to sort the positive numbers to the top, then take the absolute value with ABS to get the desired ASC/DESC.

SELECT * FROM theTable
ORDER BY SIGN(col) DESC, ABS(col)

编辑

正如Nahuel所指出的那样，以上内容将在正负之间的中间对0进行排序.要将它们与正值分组，可以使用CASE代替(或者，如果您的列仅为整数，则略带魔力的SIGN(col + 1))

As Nahuel pointed out, the above will sort 0's to the middle between positive and negative. To instead group them with the positives, you can use a CASE instead (or, if your column is only integers, the slightly magical SIGN(col + 1))

SELECT * FROM theTable
ORDER BY 
    CASE WHEN col >= 0 THEN 1 ELSE 2 END,
    ABS(col)

这篇关于ORDER BY分别为正数&amp; MySQL语句中的负数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！