巧用case when做SQL优化

      下面是一张报表的SQL实现,不用深入业务,你可以看到上面的4条SQL和下面的4条SQL其实大致相同,只是部分条件不一样,想要优化的第一感觉是如何减少扫描的次数,这个时候case when就派上用场了。

SELECT DECODE(ALL_COUNT, 0, 0, ROUND(ARRIVED_COUNT / ALL_COUNT, 4) * 100) ARRIVED_RATE
  FROM (SELECT E.COUNT_RESULT + F.COUNT_RESULT + G.COUNT_RESULT +
               H.COUNT_RESULT ARRIVED_COUNT
          FROM (SELECT COUNT(1) COUNT_RESULT
                  FROM GG_TASK_WORK_SHEET          PTWS,
                       GG_TASK_PATROL_SHEET_DETAIL PSD,
                       GG_PATROL_WORK_POINT        PPWP,
                       GG_PATROL_POINT             PPP,
                       GG_PATROL_EQUIPMENT         PPE
                 WHERE PTWS.WORK_SHEET_ID = PSD.WORK_SHEET_ID
                   AND TO_CHAR(PTWS.WORK_SHEET_ID) = PPWP.WORK_ID
                   AND PPWP.PATROL_POINT_ID = PPP.PATROL_POINT_ID
                   AND PPP.PATROL_POINT_ID = PPE.PATROL_POINT_ID
                   AND PTWS.TASK_TYPE = 4
                   AND PTWS.SHEET_TYPE = 2
                   AND PSD.PATROL_TYPE <> -1
                   AND PSD.IS_NEED_PDA = '1'
                   AND PPWP.PATROL_FLAG = 1
                   AND PTWS.IS_COMPLETE = '3'
                   AND TO_CHAR(PTWS.REAL_END_DATE, 'YYYY') = '2013'
                   AND PPE.DEVICE_ID = 1003551930) E,
               (SELECT COUNT(1) COUNT_RESULT
                  FROM GG_TASK_WORK_SHEET          PTWS,
                       GG_TASK_PATROL_SHEET_DETAIL PSD,
                       GG_PATROL_WORK_POINT        PPWP,
                       GG_PATROL_POINT             PPP,
                       GG_TASK_PATROL_OBJECT       PTPO
                 WHERE PTWS.WORK_SHEET_ID = PSD.WORK_SHEET_ID
                   AND TO_CHAR(PTWS.WORK_SHEET_ID) = PPWP.WORK_ID
                   AND PPWP.PATROL_POINT_ID = PPP.PATROL_POINT_ID
                   AND PPP.PATROL_POINT_ID = PTPO.PATROL_POINT_ID
                   AND PTWS.TASK_TYPE = 4
                   AND PTWS.SHEET_TYPE = 2
                   AND PSD.PATROL_TYPE <> -1
                   AND PSD.IS_NEED_PDA = '1'
                   AND PPWP.PATROL_FLAG = 1
                   AND PTWS.IS_COMPLETE = '3'
                   AND TO_CHAR(PTWS.REAL_END_DATE, 'YYYY') = '2013'
                   AND PTPO.DEVICE_ID = 1003551930) F,
               (SELECT COUNT(1) COUNT_RESULT
                  FROM GG_TASK_WORK_SHEET          PTWS,
                       GG_TASK_PATROL_SHEET_DETAIL PSD,
                       GG_TASK_PATROL_OBJECT       PTPO,
                       GG_PATROL_EQUIPMENT         PPE
                 WHERE PTWS.WORK_SHEET_ID = PSD.WORK_SHEET_ID
                   AND PTWS.WORK_SHEET_ID = PTPO.WORK_SHEET_ID
                   AND PTPO.PATROL_LINE_ID = PPE.PATROL_LINE_ID
                   AND PTPO.FUNCTION_LOCATION_ID = PPE.FUNCTION_LOCATION_ID
                   AND PTWS.TASK_TYPE = 4
                   AND PTWS.SHEET_TYPE = 1
                   AND PTPO.PATROL_TYPE = 1
                   AND PSD.PATROL_TYPE = -1
                   AND PSD.IS_NEED_PDA = '1'
                   AND PTPO.ARRIVE_TYPE = 1
                   AND PTWS.IS_COMPLETE = '3'
                   AND TO_CHAR(PTWS.REAL_END_DATE, 'YYYY') = '2013'
                   AND PPE.DEVICE_ID = 1003551930) G,
               (SELECT COUNT(1) COUNT_RESULT
                  FROM GG_TASK_WORK_SHEET          PTWS,
                       GG_TASK_PATROL_SHEET_DETAIL PSD,
                       GG_TASK_PATROL_OBJECT       PTPO,
                       GG_PATROL_EQUIPMENT         PPE
                 WHERE PTWS.WORK_SHEET_ID = PSD.WORK_SHEET_ID
                   AND PTWS.WORK_SHEET_ID = PTPO.WORK_SHEET_ID
                   AND PTPO.PATROL_LINE_ID = PPE.PATROL_LINE_ID
                   AND PTPO.FUNCTION_LOCATION_ID = PPE.FUNCTION_LOCATION_ID
                   AND PTWS.TASK_TYPE = 4
                   AND PTWS.SHEET_TYPE = 1
                   AND PTPO.PATROL_TYPE = 1
                   AND PSD.PATROL_TYPE <> -1
                   AND PSD.IS_NEED_PDA = '1'
                   AND PTPO.ARRIVE_TYPE = 1
                   AND PTWS.IS_COMPLETE = '3'
                   AND TO_CHAR(PTWS.REAL_END_DATE, 'YYYY') = '2013'
                   AND PPE.DEVICE_ID = 1003551930) H) ARRIVED_DEVICE,
       (SELECT A.COUNT_RESULT + B.COUNT_RESULT + C.COUNT_RESULT +
               D.COUNT_RESULT ALL_COUNT
          FROM (SELECT COUNT(1) COUNT_RESULT
                  FROM GG_TASK_WORK_SHEET          PTWS,
                       GG_TASK_PATROL_SHEET_DETAIL PSD,
                       GG_PATROL_WORK_POINT        PPWP,
                       GG_PATROL_POINT             PPP,
                       GG_PATROL_EQUIPMENT         PPE
                 WHERE PTWS.WORK_SHEET_ID = PSD.WORK_SHEET_ID
                   AND TO_CHAR(PTWS.WORK_SHEET_ID) = PPWP.WORK_ID
                   AND PPWP.PATROL_POINT_ID = PPP.PATROL_POINT_ID
                   AND PPP.PATROL_POINT_ID = PPE.PATROL_POINT_ID
                   AND PTWS.TASK_TYPE = 4
                   AND PTWS.SHEET_TYPE = 2
                   AND PSD.PATROL_TYPE = -1
                   AND PSD.IS_NEED_PDA = '1'
                   AND PTWS.IS_COMPLETE = '3'
                   AND TO_CHAR(PTWS.REAL_END_DATE, 'YYYY') = '2013'
                   AND PPE.DEVICE_ID = 1003551930) A,
               (SELECT COUNT(1) COUNT_RESULT
                  FROM GG_TASK_WORK_SHEET          PTWS,
                       GG_TASK_PATROL_SHEET_DETAIL PSD,
                       GG_PATROL_WORK_POINT        PPWP,
                       GG_PATROL_POINT             PPP,
                       GG_TASK_PATROL_OBJECT       PTPO
                 WHERE PTWS.WORK_SHEET_ID = PSD.WORK_SHEET_ID
                   AND TO_CHAR(PTWS.WORK_SHEET_ID) = PPWP.WORK_ID
                   AND PPWP.PATROL_POINT_ID = PPP.PATROL_POINT_ID
                   AND PPP.PATROL_POINT_ID = PTPO.PATROL_POINT_ID
                   AND PTWS.TASK_TYPE = 4
                   AND PTWS.SHEET_TYPE = 2
                   AND PSD.PATROL_TYPE <> -1
                   AND PSD.IS_NEED_PDA = '1'
                   AND PTWS.IS_COMPLETE = '3'
                   AND TO_CHAR(PTWS.REAL_END_DATE, 'YYYY') = '2013'
                   AND PTPO.DEVICE_ID = 1003551930) B,
               (SELECT COUNT(1) COUNT_RESULT
                  FROM GG_TASK_WORK_SHEET          PTWS,
                       GG_TASK_PATROL_SHEET_DETAIL PSD,
                       GG_TASK_PATROL_OBJECT       PTPO,
                       GG_PATROL_EQUIPMENT         PPE
                 WHERE PTWS.WORK_SHEET_ID = PSD.WORK_SHEET_ID
                   AND PTWS.WORK_SHEET_ID = PTPO.WORK_SHEET_ID
                   AND PTPO.PATROL_LINE_ID = PPE.PATROL_LINE_ID
                   AND PTPO.FUNCTION_LOCATION_ID = PPE.FUNCTION_LOCATION_ID
                   AND PTWS.TASK_TYPE = 4
                   AND PTWS.SHEET_TYPE = 1
                   AND PSD.PATROL_TYPE = -1
                   AND PSD.IS_NEED_PDA = '1'
                   AND PTPO.PATROL_TYPE = 1
                   AND PTWS.IS_COMPLETE = '3'
                   AND TO_CHAR(PTWS.REAL_END_DATE, 'YYYY') = '2013'
                   AND PPE.DEVICE_ID = 10035519304) C,
               (SELECT COUNT(1) COUNT_RESULT
                  FROM GG_TASK_WORK_SHEET          PTWS,
                       GG_TASK_PATROL_SHEET_DETAIL PSD,
                       GG_TASK_PATROL_OBJECT       PTPO,
                       GG_PATROL_EQUIPMENT         PPE
                 WHERE PTWS.WORK_SHEET_ID = PSD.WORK_SHEET_ID
                   AND PTWS.WORK_SHEET_ID = PTPO.WORK_SHEET_ID
                   AND PTPO.PATROL_LINE_ID = PPE.PATROL_LINE_ID
                   AND PTPO.FUNCTION_LOCATION_ID = PPE.FUNCTION_LOCATION_ID
                   AND PTWS.TASK_TYPE = 4
                   AND PTWS.SHEET_TYPE = 1
                   AND PTPO.PATROL_TYPE = 1
                   AND PSD.PATROL_TYPE <> -1
                   AND PSD.IS_NEED_PDA = '1'
                   AND PTWS.IS_COMPLETE = '3'
                   AND TO_CHAR(PTWS.REAL_END_DATE, 'YYYY') = '2013'
                   AND PPE.DEVICE_ID = 1003551930) D) ALL_DEVICE


使用case when改造后的SQL如下,不用运行就知道效果明显。

SELECT DECODE(ALL_COUNT, 0, 0, ROUND(ARRIVED_COUNT / ALL_COUNT, 4) * 100) ARRIVED_RATE
  FROM (SELECT (E.COUNT_RESULT1 + F.COUNT_RESULT1 + G.COUNT_RESULT1 +
               H.COUNT_RESULT1) ARRIVED_COUNT,
               (E.COUNT_RESULT2 + F.COUNT_RESULT2 + G.COUNT_RESULT2 +
               H.COUNT_RESULT2) ALL_COUNT
          FROM (SELECT COUNT(case
                               when (PSD.PATROL_TYPE <> -1 AND PPWP.PATROL_FLAG = 1) then
                                1
                               else
                                null
                             end) COUNT_RESULT1,
                       COUNT(case
                               when PSD.PATROL_TYPE = -1 then
                                1
                               else
                                null
                             end) COUNT_RESULT2
                  FROM GG_TASK_WORK_SHEET          PTWS,
                       GG_TASK_PATROL_SHEET_DETAIL PSD,
                       GG_PATROL_WORK_POINT        PPWP,
                       GG_PATROL_POINT             PPP,
                       GG_PATROL_EQUIPMENT         PPE
                 WHERE PTWS.WORK_SHEET_ID = PSD.WORK_SHEET_ID
                   AND TO_CHAR(PTWS.WORK_SHEET_ID) = PPWP.WORK_ID
                   AND PPWP.PATROL_POINT_ID = PPP.PATROL_POINT_ID
                   AND PPP.PATROL_POINT_ID = PPE.PATROL_POINT_ID
                   AND PTWS.TASK_TYPE = 4
                   AND PTWS.SHEET_TYPE = 2
                   AND PSD.IS_NEED_PDA = '1'
                   AND PTWS.IS_COMPLETE = '3'
                   AND TO_CHAR(PTWS.REAL_END_DATE, 'YYYY') = '2013'
                   AND PPE.DEVICE_ID = 1003551930) E,
               (SELECT COUNT(case
                               when PPWP.PATROL_FLAG = 1 then
                                1
                               else
                                null
                             end) COUNT_RESULT1,
                       COUNT(1) COUNT_RESULT2
                  FROM GG_TASK_WORK_SHEET          PTWS,
                       GG_TASK_PATROL_SHEET_DETAIL PSD,
                       GG_PATROL_WORK_POINT        PPWP,
                       GG_PATROL_POINT             PPP,
                       GG_TASK_PATROL_OBJECT       PTPO
                 WHERE PTWS.WORK_SHEET_ID = PSD.WORK_SHEET_ID
                   AND TO_CHAR(PTWS.WORK_SHEET_ID) = PPWP.WORK_ID
                   AND PPWP.PATROL_POINT_ID = PPP.PATROL_POINT_ID
                   AND PPP.PATROL_POINT_ID = PTPO.PATROL_POINT_ID
                   AND PTWS.TASK_TYPE = 4
                   AND PTWS.SHEET_TYPE = 2
                   AND PSD.PATROL_TYPE <> -1
                   AND PSD.IS_NEED_PDA = '1'
                   AND PPWP.PATROL_FLAG = 1
                   AND PTWS.IS_COMPLETE = '3'
                   AND TO_CHAR(PTWS.REAL_END_DATE, 'YYYY') = '2013'
                   AND PTPO.DEVICE_ID = 1003551930) F,
               (SELECT COUNT(case
                               when PTPO.ARRIVE_TYPE = 1 then
                                1
                               else
                                null
                             end) COUNT_RESULT1,
                       COUNT(case
                               when PSD.PATROL_TYPE = -1 then
                                1
                               else
                                null
                             end) COUNT_RESULT2
                  FROM GG_TASK_WORK_SHEET          PTWS,
                       GG_TASK_PATROL_SHEET_DETAIL PSD,
                       GG_TASK_PATROL_OBJECT       PTPO,
                       GG_PATROL_EQUIPMENT         PPE
                 WHERE PTWS.WORK_SHEET_ID = PSD.WORK_SHEET_ID
                   AND PTWS.WORK_SHEET_ID = PTPO.WORK_SHEET_ID
                   AND PTPO.PATROL_LINE_ID = PPE.PATROL_LINE_ID
                   AND PTPO.FUNCTION_LOCATION_ID = PPE.FUNCTION_LOCATION_ID
                   AND PTWS.TASK_TYPE = 4
                   AND PTWS.SHEET_TYPE = 1
                   AND PTPO.PATROL_TYPE = 1
                   AND PSD.PATROL_TYPE = -1
                   AND PSD.IS_NEED_PDA = '1'
                   AND PTWS.IS_COMPLETE = '3'
                   AND TO_CHAR(PTWS.REAL_END_DATE, 'YYYY') = '2013'
                   AND PPE.DEVICE_ID = 1003551930) G,
               (SELECT COUNT(case
                               when PTPO.ARRIVE_TYPE = 1 then
                                1
                               else
                                null
                             end) COUNT_RESULT1,
                       COUNT(1) COUNT_RESULT2
                  FROM GG_TASK_WORK_SHEET          PTWS,
                       GG_TASK_PATROL_SHEET_DETAIL PSD,
                       GG_TASK_PATROL_OBJECT       PTPO,
                       GG_PATROL_EQUIPMENT         PPE
                 WHERE PTWS.WORK_SHEET_ID = PSD.WORK_SHEET_ID
                   AND PTWS.WORK_SHEET_ID = PTPO.WORK_SHEET_ID
                   AND PTPO.PATROL_LINE_ID = PPE.PATROL_LINE_ID
                   AND PTPO.FUNCTION_LOCATION_ID = PPE.FUNCTION_LOCATION_ID
                   AND PTWS.TASK_TYPE = 4
                   AND PTWS.SHEET_TYPE = 1
                   AND PTPO.PATROL_TYPE = 1
                   AND PSD.PATROL_TYPE <> -1
                   AND PSD.IS_NEED_PDA = '1'
                   AND PTWS.IS_COMPLETE = '3'
                   AND TO_CHAR(PTWS.REAL_END_DATE, 'YYYY') = '2013'
                   AND PPE.DEVICE_ID = 1003551930) H) ARRIVED_DEVICE



发布了725 篇原创文章 · 获赞 62 · 访问量 178万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览