首页新闻找找看学习计划

数据汇总的子查询合并问题

0
悬赏园豆:20 [已关闭问题] 关闭于 2019-11-22 08:41

这是原始表的数据,一共是4列,分别是日期,油田ID,产液量,产油量

我的需求是查询出每天每个油田的产液量、产油量、当月累计产液量、当月累计产油量、当年累计产液量、当年累计产油量

下边是我写的sql语句:

select t.thedate,t.id_field,t.liquid,t.test_oil,

--月累计

(select sum(t1.liquid) from TJ_FIELD_PROD_DAILY t1 where t1.id_field=t.id_field and t1.thedate<=t.thedate and t1.thedate>=(select trunc(t.thedate,'mm') from dual)),
(select sum(t1.test_oil) from TJ_FIELD_PROD_DAILY t1 where t1.id_field=t.id_field and t1.thedate<=t.thedate and t1.thedate>=(select trunc(t.thedate,'mm') from dual)),

--年累计
(select sum(t1.liquid) from TJ_FIELD_PROD_DAILY t1 where t1.id_field=t.id_field and t1.thedate<=t.thedate and t1.thedate>=(select trunc(t.thedate,'yyyy') from dual)),
(select sum(t1.test_oil) from TJ_FIELD_PROD_DAILY t1 where t1.id_field=t.id_field and t1.thedate<=t.thedate and t1.thedate>=(select trunc(t.thedate,'yyyy') from dual)) 

from TJ_FIELD_PROD_DAILY t

这样一共写了4个子查询,查询效率很慢,我想问有没有办法可以把这4个子查询合并成2个,即一个子查询查两个字段,因为查询的条件都是一样的。求大神指点!

七步、的主页 七步、 | 初学一级 | 园豆:78
提问于:2017-07-03 17:29
< >
分享
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册