巧用casewhen做SQL优化(三)
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