瞄了一下,多次访问ic_xfhzk这个表,看看能否改写一下减少访问次数,可以考虑先用临时表之类的存放这个表并且把相同筛选条件的先过滤掉,减少后续操作的数据量
解决方案 »
- oracle的varchar2保留空格
- oracle登陆问题
- 'MSDAORA' 无法启动分布式事务 怎么解决这个问题啊?
- 大家好 我的Oracle OEM打不开是什么原因呢?
- ====救命啊===,500分求此问题的解决,一次只能开100分 -- SOS
- merge into 如何应用多个update
- 经常commit,会不会引起web 服务器的ThreadPool不够用?对数据库的游标有影响吗?
- WebService安全问题
- 怎样让system以sysdba/sysoper登陆?
- 菜鸟提问,关于sql*loader
- sql中直接写txt的语句
- oracle如何利用chr输出大于128的字符?
(SELECT CAST(to_date('2014-1-31','yyyy-mm-dd') AS DATE) AS datee,dwbh,xlbh,SUM(ic) AS icm,SUM(tb) AS tbm FROM (SELECT dwbh,xlbh,xfjee AS IC,nvl(0,0) AS TB FROM ic_xfhzk WHERE datee BETWEEN to_date('2014-1-1','yyyy-mm-dd') AND to_date('2014-1-31','yyyy-mm-dd') AND dwbh IN('0100','0200','0300')
UNION ALL
SELECT dwbh,xlbh,nvl(0,0) AS IC,xfjee AS TB FROM ic_xjhzk WHERE datee BETWEEN to_date('2014-1-1','yyyy-mm-dd') AND to_date('2014-1-31','yyyy-mm-dd') AND dwbh IN('0100','0200','0300')
) GROUP BY dwbh,xlbh)
(SELECT dwbh,xlbh,nvl(SUM(CASE WHEN sjlx='01'THEN xfjee END),0) AS A,nvl(SUM(CASE WHEN sjlx='A4'THEN xfjee END),0) AS C,nvl(SUM(CASE WHEN sjlx='B9'THEN xfjee END),0) AS I,nvl(SUM(CASE WHEN sjlx='A5'THEN xfjee END),0) AS D,nvl(SUM(CASE WHEN sjlx='30'THEN xfjee END),0) AS T,nvl(SUM(CASE WHEN sjlx='80'THEN xfjee END),0) AS TB
FROM
(SELECT dwbh,xlbh,sjlx,xfjee FROM ic_xfhzk WHERE datee = to_date('2014-1-31','yyyy-mm-dd') AND dwbh IN('0100','0200','0300')
UNION ALL
SELECT dwbh,xlbh,sjlx,xfjee FROM ic_xjhzk WHERE datee = to_date('2014-1-31','yyyy-mm-dd') AND dwbh IN('0100','0200','0300'))
GROUP BY dwbh,xlbh)