MySQL条件排序

Question

假设我在我的users表中有以下数据。

name  | score
John  | 2
Dave  | 8
Joe   | -3
Sally | -20
Rose  | 0

如何按分数列按以下格式对用户进行排序？

John, Dave, Rose, Sally and Joe

换句话说，那些拥有积极分数的用户应该先上升，然后是0，然后是负分数上升。

Answer 1

有一种方法使用sign()函数：

order by sign(score) desc, score asc

Answer 2

考虑：

select * from mytable order by (score < 0), (score = 0), score

DB Fiddle演示

Answer 3

也许将其分解为3个查询并组合结果？

SELECT * FROM (
    SELECT *
    FROM users
    WHERE score > 0
    ORDER BY score DESC 
)
UNION
SELECT * FROM (
    SELECT *
    FROM users
    WHERE score = 0
)
UNION
SELECT * FROM (
    SELECT *
    FROM users
    WHERE score < 0
    ORDER BY score ASC 
)