关于oracle数据库中获取版本号类数据最大值的sql

时间:2021-06-06 15:12:01

 目前还在高度加班中,但是本次内容怕自己忘记,好不容易解决的,所以赶紧先随便抽点时间记录下,也没来得及考虑效率什么的优化问题,免得以后忘记了。

测试库结构如下:

表名为 testtab

字段名为testnum,server

测试数据如下:

关于oracle数据库中获取版本号类数据最大值的sql

获取最高版本号的数据sql如下:

 1 WITH
 2   L0 as (
 3                     SELECT MAX(to_number(regexp_replace(testnum,'([[:digit:]]+).([[:digit:]]+).([[:digit:]]+)','\1'))) fir  FROM TESTTAB
 4             ),
 5     L1 as (
 6                     SELECT MAX(to_number(regexp_replace(testnum,'([[:digit:]]+).([[:digit:]]+).([[:digit:]]+)','\2'))) sec FROM TESTTAB,L0
 7                     where testnum like fir||'.%'
 8             ),
 9     L2 as (
10                     SELECT MAX(to_number(regexp_replace(testnum,'([[:digit:]]+).([[:digit:]]+).([[:digit:]]+)','\3'))) thr FROM TESTTAB,L1,L0
11                     where testnum like fir||'.'||sec||'.%'
12             )
13 select t3.* from L0,L1,L2,TESTTAB t3 where t3.testnum=fir||'.'||sec||'.'||thr;

最后得出查询结果:

关于oracle数据库中获取版本号类数据最大值的sql

sql主要用到的函数为with as,该函数主要用处是生成一个公共临时表,后面可复用,主要思路是分段比较,L0来获取第一段的最大值,L1在该基础上获取第二段的最大值,L2在之前基础上获取第三段的最大值,最后将其拼接获取最大值版本号作为查询条件去获取数据。

因为我们版本号是固定格式 A.B.C ,因此可以这样写,另外使用的数据库为oracle,mysql的暂时没空去考虑。

另外不能直接用oracle的MAX函数去比较,经粗略测试其比较方法类似于去除小数点并右补0进行比较大小,比如

关于oracle数据库中获取版本号类数据最大值的sql

如果确定第一段的数字是确定小于10的一位数,似乎是可以直接使用MAX的,只能说我没试出反例来,但是也没空去多试数据,所以不确定行不行


 ---------------------------------------------------------------------------------------------------------------2018-12-13 修订-------------------------------------------------------------------------------------------------------


 

还是和之前一样,还在加班中,昨天也就是上面写的,本身思路是没问题,分段比较肯定是最稳妥的一个办法,但是终究因为我sql水平有限,脱离了MAX函数,我不知道该如何将上面的那个sql在我需要的查询方法里用起来,今天想来想去,终究还是用了最原始的笨办法,为了能用上max函数,只好这样了,因为开发时间太紧迫,没时间给我仔细想别的办法了,项目毕竟要求12月底上线。

这次的思路是这样的:利用oracle的字符串处理,将版本号类型数据进行分段处理,每段进行左补0,每段长度补充到3位数一般足够用了,然后三段拼接,去除小数点,转为number类型,这样就可以利用上max函数了。

处理版本号的sql如下:

CREATE OR REPLACE
function dealAppMenuVersion (
        v_in varchar2)
        return number is v_out number(10,0);
        begin
            select to_number( lpad(SUBSTR(v_in, 1, INSTR(v_in,'.',1,1)-1),3,0)
                ||
                lpad(SUBSTR(v_in, INSTR(v_in,'.',1,1)+1, INSTR(v_in,'.',1,2)-INSTR(v_in,'.',1,1)-1),3,0)
                ||
                lpad(SUBSTR(v_in, INSTR(v_in,'.',1,2)+1, LENGTH(v_in)-INSTR(v_in,'.',1,2)),3,0)) into v_out from dual;
            return v_out;
        end;

测试结果如下:

关于oracle数据库中获取版本号类数据最大值的sql

在版本号可以转化为数字进行正常处理以后,接下来就方便了,先分组,然后根据版本号排序,将每个分组中的最高版本号的那行数据取出来即可。

测试代码如下:

关于oracle数据库中获取版本号类数据最大值的sql

这里得说说 ROW_NUMBER () OVER (PARTITION BY [field1] order by [field2] asc/desc) 这个oracle提供的函数,很好用,很久以前用过但是我给忘记了,这里自己记录下防止又忘记了,因为公司mysql用的比较多,oracle用的少函数很容易忘记。

这个函数大致意思上是根据field1进行分组,在该分组上按field2进行排序,还是我刚才的测试数据,效果图展示如下:

关于oracle数据库中获取版本号类数据最大值的sql