###### yhnmjkl

2017-07-03 09:37 浏览 774

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

select t.the__date,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

• 点赞
• 写回答
• 关注问题
• 收藏
• 复制链接分享
• 邀请回答

#### 2条回答默认 最新

• __如影 2017-07-04 01:59

select t.the__date,t.id__field,t.liquid,t.test__oil,__
--月累计
(select sum(t1.liquid),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),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

点赞 评论 复制链接分享
• 编程一对一服务 2017-07-05 05:24

如果不要求实时的 建议采用存储过程，定时生成统计数据表，然后在读取生成后的数据。

点赞 评论 复制链接分享