product表
Product Version
H1000 1.0
H1000 1.1
H1000 1.2
H1000 2.0
H1000 2.1
H1000 3.0
H1001 1.0
H1001 1.1
…………………………………………
H100X x.x
有如上所示资料形态的oracle table,按用户需求希望抓取产品名加小版本最大版的资料,如下所示:
Product Version
H1000 1.2
H1000 2.1
H1000 3.0
H1001 1.1
…………………………………………
本来的想法,截版本小数点左和右两部分,对左部分循环,抓右部分最大值再输出,实际作业的复杂度太高,一句sql完成不了;
后来经提示,使用group by,明显工作量下降很多,这边记录下来做个以后的参考
select c.Product,c.xx1 ||'.'||c.xxx2 as Version from
(select b.Product,b.xx1,max(b.xx2) as xxx2 from
(select Product,substr(Version,1,instr(Version,'.')-1) as xx1,substr(Version,instr(Version,'.')+1) as xx2 from product) b
where b.xx1!='0' group by b.Product,b.xx1) c
order by Product,Version