mysql - 如何在sql中对带有文本的数字进行数字排序？

Question

我的表有字母数字值。我想先按数字订购它们，但它不起作用。

下面是示例表，表名被称为method_1：

id  |    code_name | 
4     200 a
1     100 b
3     100-1 c
9     100-1/1 d
11    100-1/1/1 e
12    100-1/1/10 f
2     100-1/1/11 g
13    100-1/1/3 h
14    100-2 i
20    100-1/2 j
32    100-1/2/1 k

我使用的是 MYSQL 查询浏览器版本 1.2.11，我的 SQL 查询是SELECT * FROM method_1 order by code_name，但它无法正确排序数字。而且我也在用这个方法SELECT * FROM method_1 order by length(code_name), code_name，还是不行

实际上我希望预期的结果如下表所示：

id  |    code_name | 
1     100 b
3     100-1 c
9     100-1/1 d
11    100-1/1/1 e
13    100-1/1/3 h
12    100-1/1/10 f
2     100-1/1/11 g
20    100-1/2 j
32    100-1/2/1 k
14    100-2 i
4     200 a

使用 jarlh 答案

回答

标签： mysqlsql

Answer 1

WITH 
cte AS (SELECT id, 
               code_name,
               CONCAT('[', REGEXP_REPLACE(SUBSTRING_INDEX(code_name, ' ', 1), '[\-\/ ]', ','), ']') code,
               SUBSTRING_INDEX(code_name, ' ', -1) name
        FROM test)
SELECT id,
       code_name
FROM cte
ORDER BY code->>"$[0]" + 0,
         code->>"$[1]" + 0,
         code->>"$[2]" + 0,
         code->>"$[3]" + 0,
         name;

小提琴