mysql - SQL 中的自然排序不起作用

Question

我正在尝试在 SQL 中“自然”地对行进行排序，并且我发现了这个答案，据说它完全符合我的需要:

https://stackoverflow.com/a/17354420/7563153

但是当我尝试在“SQL Fiddle”中重新创建它时，我无法让它工作......

Here's my attempt

我做错了什么？

Answer 1

您引用的示例与您自己的试验之间的区别在于，您的数据在同一字段中组合了数字和非数字字符，因此转换为整数不起作用(它只是给出 0)。您可以通过在选择列表中添加 order by 表达式来直观地了解查询不起作用的原因，如下所示:

SELECT test
       ,test REGEXP '^[A-Za-z]+$'
       ,CAST(test as SIGNED INTEGER)
       ,CAST(REPLACE(test,'-','')AS SIGNED INTEGER)
FROM table1
ORDER BY test REGEXP '^[A-Za-z]+$'
        ,CAST(test as SIGNED INTEGER)
        ,CAST(REPLACE(test,'-','')AS SIGNED INTEGER)
        ,test

您会看到它们的计算结果全部为 0，这不会为您提供所需的排序顺序。

您需要将值的数字部分与非数字部分分开，并对它们分别进行排序。像这样的事情:

SELECT test    
FROM table1
ORDER BY substring_index(test,'-',1)
        ,CAST(substring(test, locate('-', test)+1)AS SIGNED INTEGER)
        ,test

如果您的实际数据不像示例数据那么简单明了，您可能需要进行调整。