如下SQL需查找并统计日工作时长>=12.5,请协助修改,
并优化oracle如下SQL性能,提高执行效率和减少资源占用:
SELECT ATTENDANCE_DATE 归属日,ORG_NAME3 厂区,EMPCODE 工号,EMPNAME 姓名,POSITION_RANK_NAME 职等,POSITION_NAME 职称,
ORG_NAME 课别,ORG_NAME5 部门,ORG_NAME4 处级,WEEK_ATTR 假日别,WS_SIMPLE_NAME 班别,WORK_TYPE AS "白/晚班",
CLOCK11 上班打卡时间,CLOCK12 下班打卡时间,WORK_HRS 打卡时长
FROM (
SELECT A.ATTENDANCE_DATE,HPB.ORG_NAME3,A.EMPCODE,A.EMPNAME,HPB.POSITION_RANK_NAME,HPB.POSITION_NAME,HPB.ORG_NAME,HPB.ORG_NAME5,HPB.ORG_NAME4,
DECODE(A.WEEK_ATTR,1,'平日',2,'周末',3,'节日',4,'平日周末',5,'周末平日',6,'平日节日',7,'节日平日',8,'周末节日',9,'节日周末',A.WEEK_ATTR) WEEK_ATTR,
B.WS_SIMPLE_NAME,DECODE(A.WORK_TYPE,001,'白班',002,'夜班',A.WORK_TYPE) WORK_TYPE,A.CLOCK11,A.CLOCK12,A.ZB_HRS+A.CLOCK_ADD_HRS WORK_HRS
FROM HCP.v_clk_tz_attendance_result A
JOIN HCP.V_CLK_TZ_EMPLOYEE HPB ON A.EMPCODE=HPB.EMPCODE
JOIN HCP.v_clk_work_shift B ON A.WORK_NO=B.WS_NUMBER
WHERE HPB.SALARY_TYPE='1'
AND A.ATTENDANCE_DATE>=SYSDATE-2
)
WHERE WORK_HRS>=12.5;