下面是一张报表的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