SQLite 3版本排序字符串

pbwdgjma  于 2023-01-31  发布在  SQLite
关注(0)|答案(2)|浏览(226)

这一定是一个简单的事情,但我不能弄清楚,甚至在这里看了很多例子在Stackoverflow。
我有这个字符串序列,由SQLite select生成:

SELECT sort FROM collections ORDER BY sort

1
10
11
14
15
2
3
4
5
6
6.12
6.7
6.7.13
6.7.8
9

但我需要在这个顺序(排序内容就像版本号):

1
2
3
4
5
6
6.7
6.7.8
6.7.13
6.12
9
10
11
14
15

请问,如何用SQLITE 3 select做到这一点?

vom3gejh

vom3gejh1#

SQL中的版本排序并不容易,在SQLite中更难,因为它没有很多函数来处理字符串(没有拆分)。
如果没有太多的数据,你可以用检索数据的编程语言来做,这是一个问题,因为它要求你把所有的数据都取到内存中。
您可以提供自己的custom collation to SQLite,具体方式取决于您运行查询所使用的编程语言和驱动程序,下面是一个使用Perl和DBD::SQLite的示例。

use DBD::SQLite;
require version;

$DBD::SQLite::COLLATION{as_versions} = sub {
    my($a,$b) = @_;
    return version->parse($a) <=> version->parse($b);
};

my $dbh = DBI->connect(...);
my $sth = $dbh->prepare(q{
    SELECT sort
    FROM   collections
    ORDER BY sort COLLATE as_versions
});

最后,您可以将数据更改为更适合SQLite的格式。插入版本时,添加另一列,该列是转换为数字的版本。这通常通过将每个部分转换为千分之一、百万分之一、十亿分之一等来完成。例如,“6.7.13”变为“6.007013”,“6.12”变为“6.012”。

nnsrf1az

nnsrf1az2#

该查询将字符串版本解析为三列,其中包含int版本号,并按升序(从主版本号到内部版本号)对版本号进行排序。

SELECT *, 
    CAST(SUBSTR(version, 1, INSTR(version, '.')-1) AS INTEGER) AS major,  
    CAST(SUBSTR(SUBSTR(version, INSTR(version, '.')+1) ,  1, INSTR(SUBSTR(version, INSTR(version, '.')+1), '.')-1) AS INTEGER) AS minor, 
    CAST(SUBSTR(SUBSTR(version, INSTR(version, '.')  + 1) , INSTR(SUBSTR(version, INSTR(version, '.')  + 1) , '.')  + 1) AS INTEGER) AS build
    FROM collections 
    ORDER BY major DESC, minor DESC, build DESC

相关问题