select 2z+2z2+z3+z4 as s_all
解决方案 »
- 如何在java中建立orcal数据库 并填写主键自增的trigger
- oracle的方案?
- java.sql.SQLException: ORA-01000: maximum open cursors exceeded
- 使用ado编程,遇到几个棘手的问题,向大虾们请教,急急急
- 简单问题:有些绕蒙了,如何用递归循环形成树菜单....
- 存储过程的问题???高手GG们帮忙看看!!! 哦哦哦:)
- PL/SQL问题:关于不同用户间的切换
- 问一下诸位,拿到OCP证书后每月大概能拿多少大洋?
- 一个PowerDesigner建模的问题?
- oracle 触发器小错误 ,求大神指点
- !!!!! Oracle 建数据苦时出错 !!!!!!
- 通过触发器实现从表的插入修改?
nvl(sum(z1),0)+ nvl(sum(z2),0)+ nvl(sum(z3),0)+ nvl(sum(z4),0) as sum_all
from table
from table
GROUP BY DATE_NAME
select z1,z2,z3,z4, z1+z2+z3+z4 z_all from tb
union
select sum(z1),sum(z2),sum(z3),sum(z4),sum(z1+z2+z3+z4) from tb
(select sum (z.jhje) from jh_ydzjjh z where z.ht_htgkoid=s.oid and z.nf like '2005'and z.yf like '12')as yfje,
(select sum (t.sqzf) from ht_htsq t where t.ht_htgkoid=s.oid and to_char(t.zfq,'yyyy.mm') like '2005.12')as ssqzfje,
(select sum (t.jdkhk) from ht_htsq t where t.ht_htgkoid=s.oid and to_char(t.zfq,'yyyy.mm') like '2005.12')as jdkhk ,
(select sum (t.zlkhk) from ht_htsq t where t.ht_htgkoid=s.oid and to_char(t.zfq,'yyyy.mm') like '2005.12')as zlkhk ,
(select sum (t.aqwmkhk) from ht_htsq t where t.ht_htgkoid=s.oid and to_char(t.zfq,'yyyy.mm') like '2005.12')as aqwmkhk
from ht_htgk s我就是想同时得到后面面4个字段的和
from ht_htsq t,ht_htgk s,jh_ydzjjh z
where t.ht_htgkoid=s.oid
and z.ht_htgkoid=s.oid
and z.nf like '2004'and z.yf like '01'
and to_char(t.zfq,'yyyy.mm') = '2005.12'
group by s.oid,s.htmc
sum(t.sqzf+t.jdkhk+t.zlkhk+t.aqwmkhk)是有问题的,要保证sum中每个字段没有null值,因为1 + null +2 + 3 = null; 最好是sum(nvl(t.sqzf,0)...)
sum是做了判断的,不用加nvl,忘记了最后一个sum中是有'+'的,应该用nvl
(select sum (z.jhje) from jh_ydzjjh z where z.ht_htgkoid=s.oid and z.nf like '2005'and z.yf like '08')as yfje,
(select sum (t.sqzf) from ht_htsq t where t.ht_htgkoid=s.oid and to_char(t.zfq,'yyyy.mm') like '2005.08')as sqzf,
(select sum (t.jdkhk) from ht_htsq t where t.ht_htgkoid=s.oid and to_char(t.zfq,'yyyy.mm') like '2005.08')as jdkhk ,
(select sum (t.zlkhk) from ht_htsq t where t.ht_htgkoid=s.oid and to_char(t.zfq,'yyyy.mm') like '2005.08')as zlkhk ,
(select sum (t.aqwmkhk) from ht_htsq t where t.ht_htgkoid=s.oid and to_char(t.zfq,'yyyy.mm') like '2005.08')as aqwmkhk,
(select sum(t.sqzf+t.jdkhk+t.zlkhk+t.aqwmkhk) from ht_htsq t where t.ht_htgkoid=s.oid and to_char(t.zfq,'yyyy.mm') like '2005.08')as zfje
from ht_htgk s 当zfje为空时不显示这一行怎么写呀
select *
from (select s.oid,s.htmc,
(select sum (z.jhje) from jh_ydzjjh z where z.ht_htgkoid=s.oid and z.nf like '2005'and z.yf like '08')as yfje,
(select sum (t.sqzf) from ht_htsq t where t.ht_htgkoid=s.oid and to_char(t.zfq,'yyyy.mm') like '2005.08')as sqzf,
(select sum (t.jdkhk) from ht_htsq t where t.ht_htgkoid=s.oid and to_char(t.zfq,'yyyy.mm') like '2005.08')as jdkhk ,
(select sum (t.zlkhk) from ht_htsq t where t.ht_htgkoid=s.oid and to_char(t.zfq,'yyyy.mm') like '2005.08')as zlkhk ,
(select sum (t.aqwmkhk) from ht_htsq t where t.ht_htgkoid=s.oid and to_char(t.zfq,'yyyy.mm') like '2005.08')as aqwmkhk,
(select sum(t.sqzf+t.jdkhk+t.zlkhk+t.aqwmkhk) from ht_htsq t where t.ht_htgkoid=s.oid and to_char(t.zfq,'yyyy.mm') like '2005.08')as zfje
from ht_htgk s)
where zfje is not null
用你的方法