我现在用的SQL如下:
select sum(m.ZJE) ZJE,
sum(m.XJ) XJ,
sum(m.YZYB) YZYB,
sum(m.HJYB) HJYB,
sum(m.YZLX) YZLX,
sum(m.YL) YL
from (select sum(t.totalsum_mny) ZJE, 0 XJ, 0 YZYB, 0 HJYB, 0 YZLX, 0 YL
from T_OPR_CHARGE t,
(select t.chargeno_chr
from T_OPR_CHARGE t
where t.status_int = 1
and t.operdate_dat between
to_date('2009-11-7 0:00:00', 'yyyy-mm-dd hh24:mi:ss') and
to_date('2009-11-17 23:59:59', 'yyyy-mm-dd hh24:mi:ss')) b
where t.chargeno_chr = b.chargeno_chr
union all
select 0, sum((a.paysum_mny - a.refusum_mny)) XJ, 0, 0, 0, 0
from T_OPR_PAYMENT a,
(select t.chargeno_chr
from T_OPR_CHARGE t
where t.status_int = 1
and t.operdate_dat between
to_date('2009-11-7 0:00:00', 'yyyy-mm-dd hh24:mi:ss') and
to_date('2009-11-17 23:59:59', 'yyyy-mm-dd hh24:mi:ss')) b
where a.chargeno_vchr = b.chargeno_chr
and a.paytype_int=0
union all
select 0, 0, sum(c.cyfeediarymedfee_dec) YZYB, 0, 0, 0
from T_OPR_PAYMENT a,
t_opr_charge_medicare c,
(select t.chargeno_chr
from T_OPR_CHARGE t
where t.status_int = 1
and t.operdate_dat between
to_date('2009-11-7 0:00:00', 'yyyy-mm-dd hh24:mi:ss') and
to_date('2009-11-17 23:59:59', 'yyyy-mm-dd hh24:mi:ss')) b
where a.chargeno_vchr = c.chargeno_chr
and a.paytype_int = 3
and c.ybtype_vchr = '扬州'
and c.wsinskind <> '离休'
and c.chargeno_chr = b.chargeno_chr
union all
select 0, 0, 0, sum(c.cyfeediarymedfee_dec) HJYB, 0, 0
from T_OPR_PAYMENT a,
t_opr_charge_medicare c,
(select t.chargeno_chr
from T_OPR_CHARGE t
where t.status_int = 1
and t.operdate_dat between
to_date('2009-11-7 0:00:00', 'yyyy-mm-dd hh24:mi:ss') and
to_date('2009-11-17 23:59:59', 'yyyy-mm-dd hh24:mi:ss')) b
where a.chargeno_vchr = c.chargeno_chr
and a.paytype_int = 3
and c.ybtype_vchr = '邗江'
and c.wsinskind <> '离休'
and c.chargeno_chr = b.chargeno_chr
union all
select 0, 0, 0, 0, sum(c.cyfeediarymedfee_dec) YZLX, 0
from T_OPR_PAYMENT a,
t_opr_charge_medicare c,
(select t.chargeno_chr
from T_OPR_CHARGE t
where t.status_int = 1
and t.operdate_dat between
to_date('2009-11-7 0:00:00', 'yyyy-mm-dd hh24:mi:ss') and
to_date('2009-11-17 23:59:59', 'yyyy-mm-dd hh24:mi:ss')) b
where a.chargeno_vchr = c.chargeno_chr
and a.paytype_int = 3
and c.ybtype_vchr = '扬州'
and c.wsinskind = '离休'
and c.chargeno_chr = b.chargeno_chr
union all
select 0, 0, 0, 0, 0, sum((a.paysum_mny - a.refusum_mny)) YL
from T_OPR_PAYMENT a,
(select t.chargeno_chr
from T_OPR_CHARGE t
where t.status_int = 1
and t.operdate_dat between
to_date('2009-11-7 0:00:00', 'yyyy-mm-dd hh24:mi:ss') and
to_date('2009-11-17 23:59:59', 'yyyy-mm-dd hh24:mi:ss')) b
where a.paytype_int = 1
and a.chargeno_vchr = b.chargeno_chr) m在程序中我只需要传入 t表的 t.operdate_dat 值,我想优化这段sql ,请各位帮帮忙
select sum(m.ZJE) ZJE,
sum(m.XJ) XJ,
sum(m.YZYB) YZYB,
sum(m.HJYB) HJYB,
sum(m.YZLX) YZLX,
sum(m.YL) YL
from (select sum(t.totalsum_mny) ZJE, 0 XJ, 0 YZYB, 0 HJYB, 0 YZLX, 0 YL
from T_OPR_CHARGE t,
(select t.chargeno_chr
from T_OPR_CHARGE t
where t.status_int = 1
and t.operdate_dat between
to_date('2009-11-7 0:00:00', 'yyyy-mm-dd hh24:mi:ss') and
to_date('2009-11-17 23:59:59', 'yyyy-mm-dd hh24:mi:ss')) b
where t.chargeno_chr = b.chargeno_chr
union all
select 0, sum((a.paysum_mny - a.refusum_mny)) XJ, 0, 0, 0, 0
from T_OPR_PAYMENT a,
(select t.chargeno_chr
from T_OPR_CHARGE t
where t.status_int = 1
and t.operdate_dat between
to_date('2009-11-7 0:00:00', 'yyyy-mm-dd hh24:mi:ss') and
to_date('2009-11-17 23:59:59', 'yyyy-mm-dd hh24:mi:ss')) b
where a.chargeno_vchr = b.chargeno_chr
and a.paytype_int=0
union all
select 0, 0, sum(c.cyfeediarymedfee_dec) YZYB, 0, 0, 0
from T_OPR_PAYMENT a,
t_opr_charge_medicare c,
(select t.chargeno_chr
from T_OPR_CHARGE t
where t.status_int = 1
and t.operdate_dat between
to_date('2009-11-7 0:00:00', 'yyyy-mm-dd hh24:mi:ss') and
to_date('2009-11-17 23:59:59', 'yyyy-mm-dd hh24:mi:ss')) b
where a.chargeno_vchr = c.chargeno_chr
and a.paytype_int = 3
and c.ybtype_vchr = '扬州'
and c.wsinskind <> '离休'
and c.chargeno_chr = b.chargeno_chr
union all
select 0, 0, 0, sum(c.cyfeediarymedfee_dec) HJYB, 0, 0
from T_OPR_PAYMENT a,
t_opr_charge_medicare c,
(select t.chargeno_chr
from T_OPR_CHARGE t
where t.status_int = 1
and t.operdate_dat between
to_date('2009-11-7 0:00:00', 'yyyy-mm-dd hh24:mi:ss') and
to_date('2009-11-17 23:59:59', 'yyyy-mm-dd hh24:mi:ss')) b
where a.chargeno_vchr = c.chargeno_chr
and a.paytype_int = 3
and c.ybtype_vchr = '邗江'
and c.wsinskind <> '离休'
and c.chargeno_chr = b.chargeno_chr
union all
select 0, 0, 0, 0, sum(c.cyfeediarymedfee_dec) YZLX, 0
from T_OPR_PAYMENT a,
t_opr_charge_medicare c,
(select t.chargeno_chr
from T_OPR_CHARGE t
where t.status_int = 1
and t.operdate_dat between
to_date('2009-11-7 0:00:00', 'yyyy-mm-dd hh24:mi:ss') and
to_date('2009-11-17 23:59:59', 'yyyy-mm-dd hh24:mi:ss')) b
where a.chargeno_vchr = c.chargeno_chr
and a.paytype_int = 3
and c.ybtype_vchr = '扬州'
and c.wsinskind = '离休'
and c.chargeno_chr = b.chargeno_chr
union all
select 0, 0, 0, 0, 0, sum((a.paysum_mny - a.refusum_mny)) YL
from T_OPR_PAYMENT a,
(select t.chargeno_chr
from T_OPR_CHARGE t
where t.status_int = 1
and t.operdate_dat between
to_date('2009-11-7 0:00:00', 'yyyy-mm-dd hh24:mi:ss') and
to_date('2009-11-17 23:59:59', 'yyyy-mm-dd hh24:mi:ss')) b
where a.paytype_int = 1
and a.chargeno_vchr = b.chargeno_chr) m在程序中我只需要传入 t表的 t.operdate_dat 值,我想优化这段sql ,请各位帮帮忙
解决方案 »
- 求个 Toad DBA Suite for Oracle 10.1.0 Commercial.exe 安装包
- oracle 返回主键的问题
- oracle10中sql传参数难题,求解。
- 关于apex(application express)工具
- 请牛人指教,这个function有什么问题?
- 一个关于用OMS做ORACLE备份的问题
- 几个简单的问题!
- ===========SQL语句查询效率问题,100分送上~~~在线等待===========
- 100 请教 sql/plus 连接时提示包写入失败
- impdp从linux网络导出导入数据库到window下全是错误... 求大神
- oracle oms 作业无法删除
- 修改oracle端口引发的血案
如果有多表,表之间如何关联?
如何更有效地在SQL Server论坛上提问
http://topic.csdn.net/u/20100716/19/6f132f16-20e4-418c-8dee-b99d5f86d320.html?75910