php - MySQL 字母数字值的自然排序

Question

我想对以下数据的字母 + 字母数字值进行排序:

In-Direct Labor
Level 1
Level 10
Level 11
Level 12
Level 13
Level 14
Level 15
Level 16
Level 17
Level 2
Level 3
Level 4
Level 5
Level 6
Level 7
Level 8
Level 9
Risers  Risers
Roof/Penthouse
Site

我尝试了以下解决方案，但它没有按照我想要的方式返回结果

http://www.copterlabs.com/blog/natural-sorting-in-mysql/

In-Direct Labor
Level 1
Level 2
Level 3
Level 4
Level 5
Level 6
Level 7
Level 8
Level 9
Level 10
Level 11
Level 12
Level 13
Level 14
Level 15
Level 16
Level 17
Risers  Risers
Roof/Penthouse
Site

Answer 1

可能是提取第一个词并将第二个词视为数字顺序子句:

select *
from ab
order by 
   substring(col,1, case when locate(' ',col) = 0 then 100 else locate(' ',col) end ),
   substring(col,case when locate(' ',col) = 0 then 100 else locate(' ',col) end ) + 0;

-- col contains your field.

+-----------------+
| col             |
+-----------------+
| In-Direct Labor |
| Level 1         |
| Level 2         |
| Level 3         |
| Level 4         |
| Level 5         |
| Level 6         |
| Level 7         |
| Level 8         |
| Level 9         |
| Level 10        |
| Level 11        |
| Level 12        |
| Level 13        |
| Level 14        |
| Level 15        |
| Level 16        |
| Level 17        |
| Risers  Risers  |
| Roof/Penthouse  |
| Site            |
+-----------------+
21 rows in set (0.01 sec)