自然排序SQL ORDER BY [英] Natural Sorting SQL ORDER BY

问题描述

任何人都可以帮助我确定我应该在我的ORDER BY语句中添加哪些内容以对这些值进行自然排序:

Can anyone lend me a hand as to what I should append to my ORDER BY statement to sort these values naturally:

1
10
2
22
20405-109
20405-101
20404-100
X
Z
D

理想情况下，我想要一些类似的东西:

Ideally I'd like something along the lines of:

1
2
10
22
20404-100
20405-101
20405-109
D
X
Z

我当前正在使用:

ORDER BY t.property, l.unit_number

值是l.unit_number

我尝试做l.unit_number * 1和l.unit_number + 0，但是它们没有用.

I've tried doing l.unit_number * 1 and l.unit_number + 0 but they haven't worked.

我应该做一些ORDER有条件的操作，例如Case When IsNumeric(l.unit_number)吗?

Should I be doing sort of ORDER conditional, such as Case When IsNumeric(l.unit_number)?

谢谢.

推荐答案

这可以做到:

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

the ORDER BY的4个级别:

1. REGEXP将任何alpha线分配为1，将非alpha线分配为0
2. SIGNED INT按破折号前的部分对所有数字进行排序.
删除破折号后的
3. SIGNED INT按破折号后的部分对破折号前具有相同值的任何项目进行排序.可能会替换数字2，但如果出现这种情况，则不希望将90-1与9-01对待.
4. 按字母顺序对字母进行排序.

1. REGEXP assigns any alpha line a 1 and non-alphas a 0
2. SIGNED INT Sorts all of the numbers by the portion preceding the dash.
3. SIGNED INT after removing the dash sorts any of the items with the same value before the dash by the portion after the dash. Potentially could replace number 2, but wouldn't want to treat 90-1 the same as 9-01 should the case arise.
4. Sorts the letters alphabetically.

演示: SQL小提琴

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