SQL查询优化需求：将节假日的考勤打卡时间设为NULL

现有以下SQL查询，需要优化实现：当work_days字段为holiday时，INTIME和OUTTIME字段值设为NULL。

原查询语句 #

SELECT  E.EMPID ,D.DT,to_char(D.DT,'Day') days ,case  
         when to_char(D.DT, 'fmday') = 'friday' then 'workday'
         when to_char(D.DT, 'fmday') = 'saturday' then 'workday'
         when to_char(D.DT, 'fmday') = 'monday' then 'workday' 
         when to_char(D.DT, 'fmday') = 'tuesday' then 'workday'
         when to_char(D.DT, 'fmday') = 'wednesday' then 'workday'
         when to_char(D.DT, 'fmday') = 'thursday' then 'workday' 
   else    'holiday' end work_days 
         
        ,D.DT+DBMS_RANDOM.VALUE(0,0.25/24)+ ((H.FHR+(H.FMT/60)+DECODE(H.FAM,'PM',12,0))/24) INTIME,
         D.DT+DBMS_RANDOM.VALUE(0,0.25/24)+ ((H.THR+(H.TMT/60)+DECODE(H.TAM,'PM',12,0))/24) OUTTIME
        ,E.ROWID  
  FROM EMP_SHIFT H, EMPL E,
           (SELECT LEVEL LVL , (TO_DATE('01-Dec-22','DD-MON-RR')+LEVEL-1) DT 
           FROM DUAL conNect by level  <= to_date('31-dec-22','DD-MON-RRRR')-TO_DATE('01-Dec-22','DD-MON-RRRR') +1 ) D
           WHERE E.SHIFT = H.CD

优化后的查询语句 #

SELECT  E.EMPID,
        D.DT,
        TO_CHAR(D.DT,'Day') days,
        -- 简化工作日判断逻辑
        CASE 
            WHEN TO_CHAR(D.DT, 'fmday') IN ('monday','tuesday','wednesday','thursday','friday','saturday') 
            THEN 'workday' 
            ELSE 'holiday' 
        END work_days,
        -- 节假日时INTIME置为NULL
        CASE 
            WHEN TO_CHAR(D.DT, 'fmday') IN ('monday','tuesday','wednesday','thursday','friday','saturday') 
            THEN D.DT + DBMS_RANDOM.VALUE(0,0.25/24) + ((H.FHR + (H.FMT/60) + DECODE(H.FAM,'PM',12,0))/24) 
            ELSE NULL 
        END INTIME,
        -- 节假日时OUTTIME置为NULL
        CASE 
            WHEN TO_CHAR(D.DT, 'fmday') IN ('monday','tuesday','wednesday','thursday','friday','saturday') 
            THEN D.DT + DBMS_RANDOM.VALUE(0,0.25/24) + ((H.THR + (H.TMT/60) + DECODE(H.TAM,'PM',12,0))/24) 
            ELSE NULL 
        END OUTTIME,
        E.ROWID  
FROM EMP_SHIFT H, 
     EMPL E,
     (SELECT LEVEL LVL, 
             (TO_DATE('01-Dec-22','DD-MON-RR') + LEVEL - 1) DT 
      FROM DUAL 
      CONNECT BY LEVEL <= TO_DATE('31-dec-22','DD-MON-RRRR') - TO_DATE('01-Dec-22','DD-MON-RRRR') + 1 ) D
WHERE E.SHIFT = H.CD

优化说明 #

1. 简化工作日判断：把原查询中多个WHEN分支合并成一个IN条件，代码更简洁，后续维护更方便
2. 实现节假日时间置空：对INTIME和OUTTIME分别添加CASE判断，仅当日期为工作日时计算时间值，节假日直接返回NULL

内容的提问来源于stack exchange，提问作者Shahzad Prince