1. 计算空值问题,替代+
NVL2(expr1,expr2,expr3)
Oracle在NVL()的功能上扩展,提供了NVL2函数。
功能:oracle中常用函数,如果参数表达式expr1值为NULL,则NVL2()函数返回参数表达式expr3的值;如果参数表达式expr1值不为NULL,则NVL2()函数返回参数表达式expr2的值。
即:NVL2(表达式,不为空设值,为空设值)。
2.decode
decode(value,if1,then1,if2,then2,if3,then3,...,else)
例子:
select lprq,
count(*) zs,
count(decode(jylx, 'A', 1, null)) mz,
count(decode(jylx, 'B', 1, null)) zy,
sum(jbjjzf) zcf,
sum(decode(jylx, 'A', jbjjzf, 0)) mzf,
sum(decode(jylx, 'B', jbjjzf, 0)) zyf
from (select 'A' jylx, a.mxlsh, a.jbjjzf, substr(a.lprq, 5, 2) lprq
from tb_sydb_a a
where 1 = 1
union all
select 'B' jylx, b.mxlsh, b.jbjjzf, substr(b.lprq, 5, 2) lprq
from tb_sydb_b b
where 1 = 1)
group by lprq
order by lprq