MySQL ORDER BY 定制 [英] MySQL ORDER BY Customized

问题描述

我有这些行 y DB，我想按以下顺序排序，并包含字符和数字.colum Score 是一个 varchar.WINNER 和 LOSER 也在 Score 列中.

I have these rows y DB and I would like order by but in the below order with caracters and number. The colum Score is a varchar. WINNER and LOSER are in Score colum also.

得分

WINNER
100+
100
90
80+
80
50
LOSER

推荐答案

此方法在订购时将 score 值转换为数字.我用你的数据试过，然后用你的数据加上附加值试过，两次都有效:

This approach converts the score value to a number when ordering. I tried it with your data, then with your data plus additional values, and it worked both times:

SELECT score
FROM myTable
ORDER BY
  CASE
    WHEN score = 'WINNER' THEN 100000
    WHEN score = 'LOSER' THEN -100000
    WHEN score LIKE '%+' THEN score * 100 + 99
    ELSE score * 100
 END DESC

转换如下:

• 获奖者 = 100,000
• 失败者 = -100,000
• number+ = number * 100 + 99
• number = number

• WINNER = 100,000
• LOSER = -100,000
• number+ = number * 100 + 99
• number = number

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