Oracle SQL对版本号进行排序

Question

在Oracle中，仅使用ORDER BY不会对版本号进行排序。 我的Version_Number字段被声明为VARCHAR类型，我无法更改它。 例如：以下版本：

1.20  
1.9   
1.18  
1.13  
1.5   
1.11  
2.0  
1.8   
1.3   
1.2   
1.1   
1.0   
1.10  

应该按照顺序排序

2.0   
1.20  
1.18  
1.13 
1.11 
1.10  
1.9   
1.8   
1.5  
1.3   
1.2   
1.1 
1.0   

我研究了几篇文章，但似乎没有一篇真正适合我的目的，或者答案是针对SQL Server等而不是Oracle。我发现这个特定的SQL似乎可以工作。

select version_number from mytable 
order by lpad(version_number, 4) desc;

按照以下规则对版本进行排序：

1.20    
1.18   
1.13   
1.11   
1.10   
2.0    
1.9    
1.8   
1.5     
1.3    
1.2    
1.1    
1.0    

我相信这个 SQL 语句适用于 SQL Server:

select version_number from mytable 
order by cast ('/' + replace(version_number , '.', '/') + '/' as hierarchyid) desc;

然而，这种方法在Oracle中无法使用。在Oracle中是否有hierarchyid的替代方法？


是否有人能够提供一个SQL来准确地排序这些版本呢？

我已经看到了下面提到的帖子（链接附上）。所以请不要告诉我这篇文章是重复的。
SQL sort by version "number", a string of varying length
How Can I Sort A 'Version Number' Column Generically Using a SQL Server Query
Version number sorting in Sql Server
mysql sorting of version numbers
还有很多其他的帖子。

- Arthas

Answer 1

以下是一种实现方法。首先按照.前面的数字排序，然后按照.后面的数字排序。

select version_number 
from mytable 
order by substr(version_number, 1, instr(version_number,'.')-1) desc
        ,length(substr(version_number, instr(version_number,'.')+1)) desc
        ,substr(version_number, instr(version_number,'.')+1) desc

- Vamsi Prabhala

---

谢谢你的回答。但是它按照这种方式排序：2.0， 1.9， 1.8， 1.7， 1.6， 1.5， 1.4， 1.3， 1.20， 1.2， 1.19， 1.18， 1.17， 1.16， 1.15， 1.14， 1.13， 1.12， 1.11， 1.10， 1.1， 1.0 - Arthas

你认为你能稍微调整一下它的代码，使得在版本号中没有小数点的情况下也能进行排序吗？例如：如果有一个“1”，它将显示如下排序方式：1、1.20、1.18、1.17、1.16、1.10、1.9、1.2、1.1、1.0。 - Arthas

Answer 2

此SQL支持您输入的数据以及任何包含的版本或构建数字。

with 
   inputs as (select '1.20'         as version_number from dual union all  
              select '1.9'          as version_number from dual union all
              select '1.18'         as version_number from dual union all
              select '1.13'         as version_number from dual union all
              select '1.5'          as version_number from dual union all
              select '1.11'         as version_number from dual union all
              select '2.0'          as version_number from dual union all
              select '1.8'          as version_number from dual union all
              select '1.3'          as version_number from dual union all
              select '1.2'          as version_number from dual union all
              select '1.1'          as version_number from dual union all
              select '1.0'          as version_number from dual union all
              select '1.10'         as version_number from dual union all
              select ' 3.1 '        as version_number from dual union all
              select '3.1.1000'     as version_number from dual union all
              select '3.1.1'        as version_number from dual union all
              select '3.1.100'      as version_number from dual union all
              select '3.1.2.1000'   as version_number from dual union all
              select '3.1.2.1'      as version_number from dual union all
              select '3.1.2.100 '   as version_number from dual)
,versions as  (select  trim(version_number) as version_number,
        nvl(LPAD(trim(regexp_substr(version_number, '[^.]+', 1, 1)),5,'0'),'00000') AS Major,
        nvl(LPAD(trim(regexp_substr(version_number, '[^.]+', 1, 2)),5,'0'),'00000') AS Minor, 
        nvl(LPAD(trim(regexp_substr(version_number, '[^.]+', 1, 3)),5,'0'),'00000') AS Revision, 
        nvl(LPAD(trim(regexp_substr(version_number, '[^.]+', 1, 4)),5,'0'),'00000') AS Build 
        from inputs 
        ORDER BY Major desc, Minor desc, Revision desc, Build desc)
--select * from versions; 
select version_number from versions; 

去掉--以查看中间结果。

对于OP，将“inputs as（select ... from dual）”替换为：

   inputs as (select version_number from mytable)

- Systemsplanet

Answer 3

根据Joel Coehoorn的建议here，“重构版本号存储，使每个部分都有自己的列：MajorVersion，MinorVersion，Revision，Build”。

我会重新发布，因为我发现这非常有帮助！

扩展一下，我想要获取MAX版本号，并最终使用了这个脚本和Joel的建议。

    -- GET MAX VERSION NUMBER
    SELECT
        REPLACE(vnum, ' ', '') AS versionum
    FROM
        (SELECT
            MAX(LPAD(major, 4) || '.' || LPAD(minor, 4) || '.' || LPAD(revision, 4)) AS vnum
        FROM
            my_table
        ORDER BY
            major
          , minor
          , revision
        ) tbl1

- S3DEV