mysql - MYSQL：如何对数字 DESC 进行排序，加值最高

Question

我需要一些帮助来使用纯 MYSQL 创建期望输出。我的问题是 By fire SQL 下面：

SELECT
    r.questions_id
    ,r.Empty_Peg_Count AS 'Empty_Pegs'
FROM
    Training_Core.results AS r
ORDER BY
    CAST(r.Empty_Peg_Count AS SIGNED INTEGER) DESC;

我得到以下输出：

Array
(
    [0] => Array
        (
            [questions_id] => 256332653
            [Empty_Pegs] => 2
        )

    [1] => Array
        (
            [questions_id] => 256332653
            [Empty_Pegs] => 2+
        )

    [2] => Array
        (
            [questions_id] => 256332653
            [Empty_Pegs] => 1
        )
)

我希望将+号作为高优先级的值，它应该首先显示。我想要的输出如下：

Array
(
    [0] => Array
        (
            [questions_id] => 256332653
            [Empty_Pegs] => 2+
        )

    [1] => Array
        (
            [questions_id] => 256332653
            [Empty_Pegs] => 2
        )

    [2] => Array
        (
            [questions_id] => 256332653
            [Empty_Pegs] => 1
        )
)

谁能帮助我如何通过 SQL 查询获得此结果？

感谢提前！

标签： mysql

Answer 1

您可以使用第二order by条规则：

ORDER BY
    CAST(r.Empty_Peg_Count AS SIGNED INTEGER) DESC
    ,r.Empty_Peg_Count LIKE '%+%' DESC;

这会将带有 a 的条目推+到顶部。