Oracle SQL对版本号进行排序 [英] Oracle SQL to Sort Version Numbers

问题描述

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

In Oracle, just using the ORDER BY does not sort version numbers. My Version_Number field is declared as a VARCHAR and I cannot change it. For Example: The following versions :

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.

I have researched several posts but none of them seem to really serve my purpose or the answers were intended for SQL Server, etc and not Oracle. I came across this particular sql which seemed to look like it worked.

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

以这种方式对版本进行排序:

which sorted the versions in this fashion:

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:

I believe this sql statement works for SQL Server :

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

但是，这不适用于Oracle.在Oracle中，hierarchicalid可以替代吗?


有没有人能提出SQL来对这些版本进行准确排序?

However, this does not work with Oracle. Is there an alternative to hierarchyid in Oracle?


Is there anyone who can come up with a SQL to sort these versions accurately?

我已经看过以下提到的帖子(附链接).因此，请不要告诉我这篇文章是重复的.
按版本"number"排序的SQL，字符串为长度不一
如何进行排序通常使用SQL Server查询的版本号"列
在Sql Server中进行版本号排序
版本号的mysql排序
还有更多

I have already seen the posts mentioned below (links attached). So kindly do not tell me that this post is a duplicate.
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
and many more.

推荐答案

这是一种实现方法.首先按.之前的数字排序，然后按.

This is one way to do it. First order by the number before . and then by the numbers after .

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

这篇关于Oracle SQL对版本号进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！