mysql 用字母和单词对单元格进行排序

Question

我得到的单元格可能包含以下数据(错误数)

1
2
3
PASS
NoFileFound
NoLog
99
10
2

我想按升序和降序排序，其中我将 PASS 视为值 0，任何其他基于文本的值应视为值 1 错误。截至目前，这些单元格以“文本”形式存储在 mysql 数据库中。 MYSQL 如何做到这一点？我需要做哪些改变？

Answer 1

试试这个:

SELECT Number FROM (
   SELECT IF(valueField='PASS',0,1) as Number  FROM TableMix 
   WHERE concat('',valueField * 1) <> valueField ) A
UNION ALL
SELECT Number FROM (
  SELECT CAST(valueField as UNSIGNED) as Number  FROM TableMix 
  WHERE concat('',valueField * 1) = valueField ) B
ORDER BY Number

查看我的SqlFiddle Demo

这个包含原始值:

SELECT Number, valueField FROM (
   SELECT IF(valueField='PASS',0,1) as Number, valueField  FROM TableMix 
   WHERE concat('',valueField * 1) <> valueField ) A
UNION ALL
SELECT Number, valueField FROM (
  SELECT CAST(valueField as UNSIGNED) as Number, valueField  FROM TableMix 
  WHERE concat('',valueField * 1) = valueField ) B
ORDER BY Number

查看此Demo .