高效sql写法样例

最优算法

SELECT SUM(DECODE(TO_CHAR(ZFDJRQ,'YYYY-MM-DD'),TO_CHAR(SYSDATE-1,'YYYY-MM-DD'),ZFZFJE)) ZTJE,
       SUM(DECODE(TO_CHAR(ZFDJRQ,'YYYY-MM-DD'),TO_CHAR(SYSDATE,'YYYY-MM-DD'),ZFZFJE)) JTJE,
       SUM(DECODE(TO_CHAR(ZFDJRQ,'WW'),TO_CHAR(SYSDATE,'WW'),ZFZFJE)) BZJE,
       SUM(ZFZFJE) BYJE,
       SUM(DECODE(SIGN(ZFDJRQ-TO_DATE('2016-12-30 15:00:00','YYYY-MM-DD HH24:MI:SS'))
                  ||SIGN(TO_DATE('2016-12-30 15:29:59','YYYY-MM-DD HH24:MI:SS')-ZFDJRQ),
                  '11',ZFZFJE
                 )
          ) SDJE
FROM 
(SELECT ZFDJRQ,ZFZFFS,ZFZFJE FROM MRTZFMX0
 WHERE TO_CHAR(ZFDJRQ,'YYYY-MM-DD')>=TO_CHAR(SYSDATE,'YYYY-MM')||'-01'
       AND TO_CHAR(ZFDJRQ,'YYYY-MM-DD')<=TO_CHAR(SYSDATE,'YYYY-MM-DD')
) A,
(SELECT ZFBM00,ZFLX00,ZFMC00 FROM MRTZFGL0
 WHERE ZFLX00='1') B
WHERE A.ZFZFFS=B.ZFBM00 

次优算法

SELECT SUM(ZTJE) ZTJE,SUM(JTJE) JTJE,
       SUM(BZJE) BZJE,SUM(BYJE) BYJE
FROM 
(
SELECT SUM(ZFZFJE) ZTJE,0 JTJE,0 BZJE,0 BYJE
FROM 
(SELECT ZFZFFS,ZFZFJE FROM MRTZFMX0
 WHERE TO_CHAR(ZFDJRQ,'YYYY-MM-DD')=TO_CHAR(SYSDATE-1,'YYYY-MM-DD')
) A,
(SELECT ZFBM00,ZFLX00,ZFMC00 
 FROM MRTZFGL0
 WHERE ZFLX00='1') B
WHERE A.ZFZFFS=B.ZFBM00

UNION ALL

SELECT 0 ZTJE,SUM(ZFZFJE) JTJE,0 BZJE,0 BYJE
FROM 
(SELECT ZFZFFS,ZFZFJE FROM MRTZFMX0
 WHERE TO_CHAR(ZFDJRQ,'YYYY-MM-DD')=TO_CHAR(SYSDATE,'YYYY-MM-DD')
) A,
(SELECT ZFBM00,ZFLX00,ZFMC00 
 FROM MRTZFGL0
 WHERE ZFLX00='1') B
WHERE A.ZFZFFS=B.ZFBM00

UNION ALL

SELECT 0 ZTJE,0 JTJE,SUM(ZFZFJE) BZJE,0 BYJE
FROM 
(SELECT ZFZFFS,ZFZFJE FROM MRTZFMX0
 WHERE TO_CHAR(ZFDJRQ,'WW')=TO_CHAR(SYSDATE,'WW')
) A,
(SELECT ZFBM00,ZFLX00,ZFMC00 
 FROM MRTZFGL0
 WHERE ZFLX00='1') B
WHERE A.ZFZFFS=B.ZFBM00

UNION ALL

SELECT 0 ZTJE,0 JTJE,0 BZJE,SUM(ZFZFJE) BYJE
FROM 
(SELECT ZFZFFS,ZFZFJE FROM MRTZFMX0
 WHERE TO_CHAR(ZFDJRQ,'MM')=TO_CHAR(SYSDATE,'MM')
) A,
(SELECT ZFBM00,ZFLX00,ZFMC00 
 FROM MRTZFGL0
 WHERE ZFLX00='1') B
WHERE A.ZFZFFS=B.ZFBM00

)