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_