oracle中也有这样的语句的 SELECT SUM(CASE WHEN zpd <> selfjwd THEN then round(bwzongzdgl)+round(djzongzdgl) else 0 end) from emp e;
我想请问一下这个DECODE 的具体用法是什么
decode(exp,search1,result1,search2,result2,...searchn,resultn,default)A DECODE function compares expr to each search value one by one. If expr is equal to a search, Oracle returns the corresponding result. If no match is found, Oracle returns default, or, if default is omitted, returns null.
jiezhi(西域浪子) 兄 多谢指教改了一半了下面关于LIKE的部分应该怎么改??? select sum(round(zzxgl,0)), sum(decode(substr(zpd,1,1),selftlj,0,round(zzxgl,0))), sum(decode(zpd,selfjwd,0,round(zzxgl,0))), sum(round(bwzongzdgl,0)+round(djzongzdgl,0)), sum(decode(substr(zpd,1,1),selftlj,0,round(bwzongzdgl,0)+round(djzongzdgl,0))), sum(decode(zpd,selfjwd,0,round(bwzongzdgl,0)+round(djzongzdgl,0))) sum(decode(gzlh not like '%9')and(gzlh not like '%8')and(gzlh not like '%6') then bwgl else 0 end), clgl=sum(case when (gzlh not like '%9')and(gzlh not like '%8')and(gzlh not like '%6') then clgl else 0 end), djgl=sum(case when (gzlh not like '%9')and(gzlh not like '%8')and(gzlh not like '%6') then djgl else 0 end), dlfg=sum(case when (gzlh like '%9') then bwgl+clgl+djgl else 0 end), bjgl=sum(case when (gzlh like '%8')or(gzlh like '%6') then bwgl+clgl+djgl else 0 end), zhuandiao=sum(case when (gzlh in ('80','81')) then zongdiao*20 else 0 end), dcdj=sum(case when (gzlh in ('86','87')) then zongdiao*20 else 0 end), /*zongdiao=sum(case when (gzlh not in ('80','81','86','87')) then zongdiao*20 else 0 end)*/ zongdiao=0 into zzxgl,zzxgl_wj,zzxgl_wd,zongzdgl,zongzdgl_wj,zongzdgl_wd from baodannew where jxlb=jxlb1 and tjrq>=startrq and tjrq<=endrq and (gzlh<'10');
select sum(round(zzxgl,0)), sum(decode(substr(zpd,1,1),selftlj,0,round(zzxgl,0))), sum(decode(zpd,selfjwd,0,round(zzxgl,0))), sum(round(bwzongzdgl,0)+round(djzongzdgl,0)), sum(decode(substr(zpd,1,1),selftlj,0,round(bwzongzdgl,0)+round(djzongzdgl,0))), sum(decode(zpd,selfjwd,0,round(bwzongzdgl,0)+round(djzongzdgl,0))), sum(decode_like(gzlh,'%9',0,'%8',0,'%6',0,bwgl)) 不对啊兄弟 into zzxgl,zzxgl_wj,zzxgl_wd,zongzdgl,zongzdgl_wj,zongzdgl_wd,bwgl from baodannew where jxlb=jxlb1 and tjrq>=startrq and tjrq<=endrq and (gzlh<'10');end;
select sum(round(zzxgl,0)), sum(decode(substr(zpd,1,1),selftlj,0,round(zzxgl,0))), sum(decode(zpd,selfjwd,0,round(zzxgl,0))), sum(round(bwzongzdgl,0)+round(djzongzdgl,0)), sum(decode(substr(zpd,1,1),selftlj,0,round(bwzongzdgl,0)+round(djzongzdgl,0))), sum(decode(zpd,selfjwd,0,round(bwzongzdgl,0)+round(djzongzdgl,0))), sum(decode(substr(gzlh,2,1),9,0,6,0,8,0,bwgl)) into zzxgl,zzxgl_wj,zzxgl_wd,zongzdgl,zongzdgl_wj,zongzdgl_wd,bwgl from baodannew where jxlb=jxlb1 and tjrq>=startrq and tjrq<=endrq and (gzlh<'10');end;
dcdj=sum(case when (gzlh in ('86','87')) then zongdiao*20 else 0 end), zongdiao=sum(case when (gzlh not in ('80','81','86','87')) then zongdiao*20 else 0 end)这两句啊 我改成这个样子了如果'zongdiao*20'这句很长的话有什么办法优化 或者根本就有别的写法? sum(decode(gzlh,'80',zongdiao*20,'81',zongdiao*20,0)), sum(decode(gzlh,'86',zongdiao*20,'87',zongdiao*20,0))
问题一: 这句话 sum(decode(gzlh not like '%9')and(gzlh not like '%8')and(gzlh not like '%6') then bwgl else 0 end) 可以这样: sum(decode(instr(gzlh,'9')+instr(gzlh,'8')+instr(gzlh,'6'),0,bwgl,0)) 其它语句类以以上。问题二: dcdj=sum(case when (gzlh in ('86','87')) then zongdiao*20 else 0 end), zongdiao=sum(case when (gzlh not in ('80','81','86','87')) then zongdiao*20 else 0 end) 修改如下: dcdj=sum(decode(to_number(gzlh)-86)*(to_number(gzlh)-87),0,zongdiao*20,0), zongdiao=sum(decode(instr(gzlh,'80')+intstr(gzlh,'81')+instr(gzlh,'86')+instr(gzlh,'87'),0,zongdiao*20,0)其实方法比较多,主要对oracle函数熟练运用.
SELECT SUM(CASE WHEN zpd <> selfjwd THEN then round(bwzongzdgl)+round(djzongzdgl) else 0 end) from emp e;
returns default, or, if default is omitted, returns null.
多谢指教改了一半了下面关于LIKE的部分应该怎么改??? select sum(round(zzxgl,0)),
sum(decode(substr(zpd,1,1),selftlj,0,round(zzxgl,0))),
sum(decode(zpd,selfjwd,0,round(zzxgl,0))),
sum(round(bwzongzdgl,0)+round(djzongzdgl,0)),
sum(decode(substr(zpd,1,1),selftlj,0,round(bwzongzdgl,0)+round(djzongzdgl,0))),
sum(decode(zpd,selfjwd,0,round(bwzongzdgl,0)+round(djzongzdgl,0)))
sum(decode(gzlh not like '%9')and(gzlh not like '%8')and(gzlh not like '%6') then bwgl else 0 end),
clgl=sum(case when (gzlh not like '%9')and(gzlh not like '%8')and(gzlh not like '%6') then clgl else 0 end),
djgl=sum(case when (gzlh not like '%9')and(gzlh not like '%8')and(gzlh not like '%6') then djgl else 0 end),
dlfg=sum(case when (gzlh like '%9') then bwgl+clgl+djgl else 0 end),
bjgl=sum(case when (gzlh like '%8')or(gzlh like '%6') then bwgl+clgl+djgl else 0 end),
zhuandiao=sum(case when (gzlh in ('80','81')) then zongdiao*20 else 0 end),
dcdj=sum(case when (gzlh in ('86','87')) then zongdiao*20 else 0 end),
/*zongdiao=sum(case when (gzlh not in ('80','81','86','87')) then zongdiao*20 else 0 end)*/
zongdiao=0
into zzxgl,zzxgl_wj,zzxgl_wd,zongzdgl,zongzdgl_wj,zongzdgl_wd
from baodannew
where jxlb=jxlb1 and tjrq>=startrq and tjrq<=endrq
and (gzlh<'10');
decode_like(gzlh,'%9',0,'%8',0,'%6',0,clgl)
select sum(round(zzxgl,0)),
sum(decode(substr(zpd,1,1),selftlj,0,round(zzxgl,0))),
sum(decode(zpd,selfjwd,0,round(zzxgl,0))),
sum(round(bwzongzdgl,0)+round(djzongzdgl,0)),
sum(decode(substr(zpd,1,1),selftlj,0,round(bwzongzdgl,0)+round(djzongzdgl,0))),
sum(decode(zpd,selfjwd,0,round(bwzongzdgl,0)+round(djzongzdgl,0))),
sum(decode_like(gzlh,'%9',0,'%8',0,'%6',0,bwgl))
不对啊兄弟
into zzxgl,zzxgl_wj,zzxgl_wd,zongzdgl,zongzdgl_wj,zongzdgl_wd,bwgl
from baodannew
where jxlb=jxlb1 and tjrq>=startrq and tjrq<=endrq
and (gzlh<'10');end;
create or replace procedure GetNewJb3(jxlb1 char,startrq date,endrq date,p_rc out pkg_pub.getnewjb1)
as
sort char(4) ; /*排序*/
head varchar2(20); /*统计类别*/
trans_head char(4) ; /*传输头部*/
zzxgl number(15,0); /*总走行公里*/
zzxgl_wj number(15,0); /*外局*/
zzxgl_wd number(15,0); /*外段*/
bwgl number(15,0); /*本务机走行公里*/
hjgl number(15,0); /*合计*/
clgl number(15,0); /*重联*/
djgl number(15,0); /*单机*/
dlfg number(15,0); /*动力附挂*/
bjgl number(15,0); /*补机*/
zhuandiao number(15,0); /*专调*/
dcdj number(15,0); /*调车单机*/
zongdiao number(15,0); /*列车调车*/
other number(15,0); /*其他*/
zongzdgl number(15,0); /*总重吨*/
zongzdgl_wj number(15,0); /*外局*/
zongzdgl_wd number(15,0); /*外局*/
selftlj char(4) ; /*本公司*/
selfjwd char(4) ; /*本机务段*/
begin
/*清除数据*/
delete from NewJb3 where jxlb=jxlb1;
/*取数据*/
/*select selftlj=tlj,selfjwd=jwdcode from system暂时去掉这个*/
/*统计客运*/
sort:='3';
trans_head:='103';
head:=' 客运';
select sum(round(zzxgl,0)),
sum(decode(substr(zpd,1,1),selftlj,0,round(zzxgl,0))),
sum(decode(zpd,selfjwd,0,round(zzxgl,0))),
sum(round(bwzongzdgl,0)+round(djzongzdgl,0)),
sum(decode(substr(zpd,1,1),selftlj,0,round(bwzongzdgl,0)+round(djzongzdgl,0))),
sum(decode(zpd,selfjwd,0,round(bwzongzdgl,0)+round(djzongzdgl,0))),
sum(decode(substr(gzlh,2,1),9,0,6,0,8,0,bwgl))
into zzxgl,zzxgl_wj,zzxgl_wd,zongzdgl,zongzdgl_wj,zongzdgl_wd,bwgl
from baodannew
where jxlb=jxlb1 and tjrq>=startrq and tjrq<=endrq and (gzlh<'10');end;
ORACLE下面又如何表示真晕啊
我估计在其他数据库下面超级简单的语句到这下面就要大费工夫
只是需要说明的是:定义变量的时候最好使用如下格式:
v_field tableName.fieldName%type;
这样表的字段类型变了,存储过程也不用改。
zongdiao=sum(case when (gzlh not in ('80','81','86','87')) then zongdiao*20 else 0 end)这两句啊
我改成这个样子了如果'zongdiao*20'这句很长的话有什么办法优化
或者根本就有别的写法?
sum(decode(gzlh,'80',zongdiao*20,'81',zongdiao*20,0)),
sum(decode(gzlh,'86',zongdiao*20,'87',zongdiao*20,0))
这句话
sum(decode(gzlh not like '%9')and(gzlh not like '%8')and(gzlh not like '%6') then bwgl else 0 end)
可以这样:
sum(decode(instr(gzlh,'9')+instr(gzlh,'8')+instr(gzlh,'6'),0,bwgl,0))
其它语句类以以上。问题二:
dcdj=sum(case when (gzlh in ('86','87')) then zongdiao*20 else 0 end),
zongdiao=sum(case when (gzlh not in ('80','81','86','87')) then zongdiao*20 else 0 end)
修改如下:
dcdj=sum(decode(to_number(gzlh)-86)*(to_number(gzlh)-87),0,zongdiao*20,0),
zongdiao=sum(decode(instr(gzlh,'80')+intstr(gzlh,'81')+instr(gzlh,'86')+instr(gzlh,'87'),0,zongdiao*20,0)其实方法比较多,主要对oracle函数熟练运用.