一、简介
最近在做统计查询时,遇到一个数值0.2查询出来却显示.2的问题,于是查找原因,发现oracle对数值0.n转换成char类型的时候会自动忽略前面的0。本文将通过实际案例讲解怎么解决这种问题。
实际项目中一个统计示例sql:
select r.xqid,r.yf,r.roomid,r.floorid,r.flowid,r.campus_key,r.roombh,listagg(to_char(r.class_name),',') within group(order by r.class_name) class_name,
listagg(to_char(r.fdymc || '(' || r.fdykf || ')'),',') within group(order by r.class_name) fdymc,
r.kfcs,r.xnxqmc,r.depname,r.xykf from (select xqid,yf,roomid,floorid,flowid,campus_key,yxid,yxmc,roombh,class_name,fdymc,fdyid,kfcs,fdykf,xq.xnxqmc, depname,depid,
case when xypm=1 then xykf+0.4 when xypm = 2 then xykf+0.3 when xypm=3 then xykf+0.2 when xypm=4 then xykf+0.1 else xykf end as xykf
from (
select xqid,yf,roomid,floorid,flowid,campus_key,yxid,yxmc,roombh,wm_concat(CLASS_NAME) class_name ,fdymc,fdyid,sum(kfcs)as kfcs ,(kfcs*0.05) as fdykf,fjs*0.01 as xykf,qszs, fjs,depid,depname,
round(fjs/qszs,5) as bjgzb,rank() over(partition by yf order by round(fjs/qszs,5) desc )as xypm
from (
select t.xqid,t.yf,t.roomid,t.floorid,t.flowid,t.campus_key,t.kfcs,t2.yxid,t2.yxmc,t1.roombh,t1.CLASS_NAME,t2.fdymc,t2.fdyid,cy.fjs,cy1.qszs,d.department_name as depname,d.department_key as depid
from (select yf,roomid,floorid,flowid,campus_key,xqid,count(*) kfcs from ZHXG_GY_RCGL_HNSXY_WSJCDJ t left join ZHXG_XTSZ_JXZ t1 on t.jxzid = t1.pkid where dj='2' group by xqid,yf,roomid,floorid,flowid,campus_key) t
left join v_zhxg_gy_gyjbxx_cyfp t1 on t.roomid = t1."ROOMID"
left join wp_kdb_department d on d.department_key = t1.fjszdepid
left join ( select r.depid,count(*) fjs from
(select t.depid,t.roomid from zhxg_gy_gyjbxx_cyfp t
where t.roomid in (select w.roomid from ZHXG_GY_RCGL_HNSXY_WSJCDJ w group by w.roomid)
group by t.depid,t.roomid) r group by r.depid
) cy on t1."DEPID" = cy.depid
left join ( select r.depid,count(*) qszs from
(select t.depid,t.roomid from zhxg_gy_gyjbxx_cyfp t group by t.depid,t.roomid) r group by r.depid
) cy1 on t1."DEPID" = cy1.depid
left join v_zhxg_xgdw_fdybj t2 on t1."BJID" = t2.bjid
where t1."STUID" is not null and t1."DEPID" = d.department_key
group by t.xqid,t.yf,t.roomid,t.floorid,t.flowid,t.campus_key,t.kfcs,t2.yxid,t2.yxmc,t1.roombh,t1.CLASS_NAME,t2.fdymc,t2.fdyid,cy.fjs,cy1.qszs,d.department_key,d.department_name
) group by xqid,yf,roomid,floorid,flowid,campus_key,yxid,yxmc,roombh,fdymc,fdyid,fjs,qszs,kfcs,depid,depname
) left join ZHXG_XTSZ_XQXX xq on xqid = xq.pkid) r group by r.xqid,r.yf,r.roomid,r.floorid,r.flowid,r.campus_key,r.roombh,r.kfcs,r.xnxqmc,r.xykf,r.depname
;
查询结果:可以看到0.2被显示成了.2。
下面讲解怎么解决这种问题,使其正常输出展示在页面。
二、第一种方法
使用case..when判断是否以''.''开头,如果是则在前面加一个0,否则直接输出
case when r.fdykf like '.%' then ( '0'|| r.fdykf) else to_char( '0'|| r.fdykf) end
三、第二种方法
使用decode结合substr判断第一个字符是否是".",如果是则在前面拼接一个"0",否则直接输出
decode(substr(r.fdykf,1,1),'.','0'||r.fdykf,r.fdykf)
四、总结
本文是作者在实际项目中使用oracle统计数值数据时踩到的坑以及解决方法,仅供大家学习参考,共同学习共同进步。