按拆分列排序 [英] Order By Split Column

问题描述

我有一栏叫它id:

4.1.2.10.0
4.1.2.3.0
4.1.2.4.0
6.1.20.0.0
6.1.3.0.0
...
etc...

我想做的是包含一个ORDER BY语句，该语句将 字符串，变成这样:

What I'd like to do is include an ORDER BY statement that splits the string, to become like this:

4.1.2.3.0
4.1.2.4.0
4.1.2.10.0
6.1.3.0.0
6.1.20.0.0
...
etc....

我该怎么做?

推荐答案

将field1替换为实际的字段名称:

Replace field1 with the actual field name:

select      *
from        tbl
order by    CInt(mid(field1,1,instr(1,field1,'.')-1)),
            CInt(mid(field1,instr(1,field1,'.')+1,instr(2,field1,'.')-1)),
            CInt(mid(field1,instr(3,field1,'.')+1,instr(4,field1,'.')-1)),
            CInt(mid(field1,instr(5,field1,'.')+1,instr(6,field1,'.')-1))

正如戈登在评论中指出的那样，这很容易出错.可能要等待更好的答案.

As noted by Gordon in the comments, this is prone to error. May want to await a better answer.

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