关于一些函数的用法．row_number()over(partition by)和case when then

如下SQL，D.POINT_TIME - TRUNC(D.POINT_TIME)再去掉1秒：WITH work_s AS ( SELECT A.EMP_CODE, A.EMPNAME, A.WORK_DATE, B.WS_NUMBER, B.WS_SIMPLE_NAME, C.LAST_TIME_TOTAL, D.POINT_TIME, D.ADV_TIME, D.OVER_TIME, D.WP_TYPE, D.IS_NEXT_DAY, -- 修复时间计算 CASE WHEN D.WP_TYPE = 2 AND D.IS_NEXT_DAY = 1 THEN TRUNC(A.WORK_DATE) + 1 + (D.POINT_TIME - TRUNC(D.POINT_TIME)) ELSE TRUNC(A.WORK_DATE) + (D.POINT_TIME - TRUNC(D.POINT_TIME)) END AS base_time, TRUNC(A.WORK_DATE) + (D.POINT_TIME - TRUNC(D.POINT_TIME)) + CASE WHEN D.WP_TYPE = 2 THEN (C.LAST_TIME_TOTAL + CASE WHEN B.WS_NUMBER IN ('035A','035B') THEN 90 WHEN B.WS_NUMBER = '005A' THEN 60 ELSE 120 END)/1440 ELSE 0 END + CASE WHEN D.IS_NEXT_DAY = 1 THEN 1 ELSE 0 END AS overpoint_time, CASE WHEN B.WS_NUMBER = '005A' AND D.WP_TYPE = 2 THEN TRUNC(A.WORK_DATE) + (D.POINT_TIME - TRUNC(D.POINT_TIME)) + (C.LAST_TIME_TOTAL + 240)/1440 END AS overpoint_time2 FROM HCP.v_clk_tz_emp_workday_info A JOIN HCP.v_clk_work_shift B ON A.WS_ID = B.ID JOIN HCP.v_clk_work_block C ON A.WS_ID = C.WS_ID JOIN HCP.v_clk_work_point D ON C.ID = D.WB_ID WHERE A.WORK_DATE = TRUNC(SYSDATE-1) ), attendance_data AS ( SELECT DECODE(HPB.WORK_PLACE, 0, 'LSP1', 1, 'LSP2', 2, 'LSP3') AS MASTER_NO, DECODE(HPB.WORK_PLACE, 0, '一园', 1, '二园', 2, '三园') AS WORK_PLACE, HPB.EMPCODE, HPB.EMPNAME, FF.FGT02, FF.FGT08, SUBSTR(FF.FGT08,3,1) AS card_type, HPB.RANK_NAME, HPB.ORG_NAME, DECODE(H.SALARY_TYPE,1,'月薪',2,'计时',3,'计件') AS SALARY_TYPE FROM FINGER5.FGT_FILE FF JOIN HCP.TZ_EMP_IN HPB ON FF.FGT10 = HPB.EMPCODE JOIN HCP.v_clk_tz_employee H ON FF.FGT10 = H.EMPCODE JOIN work_s A ON A.EMP_CODE = FF.FGT10 WHERE FF.FGT02 >= TRUNC(SYSDATE-1) AND FF.FGT02 < TRUNC(SYSDATE) -- 优化为闭开区间 AND FF.FGT03 <> '4' AND LENGTH(FF.FGT08) = 5 AND SUBSTR(FF.FGT08,3,1) IN ('1','2') AND ( (SUBSTR(FF.FGT08,3,1) = '1' AND A.WP_TYPE = 1 AND FF.FGT02 BETWEEN A.base_time AND A.base_time + INTERVAL '1' MINUTE) OR (SUBSTR(FF.FGT08,3,1) = '2' AND A.WP_TYPE = 2 AND ( CASE WHEN H.SALARY_TYPE = 1 THEN CASE WHEN FF.FGT02 BETWEEN A.base_time AND A.base_time + INTERVAL '1' MINUTE THEN 1 ELSE 0 END WHEN A.WS_NUMBER = '005A' THEN CASE WHEN FF.FGT02 BETWEEN A.base_time AND A.base_time + INTERVAL '1' MINUTE OR FF.FGT02 BETWEEN A.overpoint_time AND A.overpoint_time + INTERVAL '1' MINUTE OR FF.FGT02 BETWEEN A.overpoint_time2 AND A.overpoint_time2 + INTERVAL '1' MINUTE THEN 1 ELSE 0 END ELSE CASE WHEN FF.FGT02 BETWEEN A.base_time AND A.base_time + INTERVAL '1' MINUTE OR FF.FGT02 BETWEEN A.overpoint_time AND A.overpoint_time + INTERVAL '1' MINUTE THEN 1 ELSE 0 END END = 1 )) ) ), attendance_filtered AS ( SELECT MASTER_NO, WORK_PLACE, EMPCODE, EMPNAME, FGT02, FGT08, card_type, DECODE(card_type, '1', '上班卡', '2', '下班卡') AS status, RANK_NAME, ORG_NAME, SALARY_TYPE, ROW_NUMBER() OVER ( PARTITION BY EMPCODE, card_type ORDER BY FGT02 ASC ) AS rank -- 按员工和打卡类型分区，取最早记录 FROM attendance_data ), tz_opr AS ( SELECT KLV.MEANING, KLV.DESCRIPTION, KLV.REVERSE2, KLV.REVERSE1, KLV.REVERSE3 IP, KLV.REVERSE4 FROM HCP.KS_LOOKUP_VALUE KLV WHERE KLV.LOOKUP_TYPE = 'KQ_SEPARATE_MACHINE' AND KLV.ENABLED = 'Y' ) SELECT MASTER_NO, WORK_PLACE AS MASTER_NAME, EMPCODE AS ID_NO_SZ, EMPNAME AS NAME_SZ, FGT02 AS RECORDTIME, FGT08 AS MACHINE_NO, trunc(SYSDATE-1) AS CDAY, -- 修改为TRUNC确保纯日期输出 A.REVERSE4 AS MACHINE_LOC, status AS SHIFTTYPE, RANK_NAME AS TITLE_NO, HCP.SF_LSHR_NO_GET(EMPCODE, 18) AS TITLE_NAME, SALARY_TYPE, HCP.SF_LSHR_NO_GET(EMPCODE, 2) AS SEGMENT_NO, HCP.SF_LSHR_NO_GET(EMPCODE, 13) AS SEGMENT_NAME, SUBSTR(HCP.SF_LSHR_NO_GET(EMPCODE, 15), LENGTH(HCP.SF_LSHR_NO_GET(EMPCODE, 15))-5) AS DEPT_NO, -- 取后6位 HCP.SF_LSHR_NO_GET(EMPCODE, 15) AS DEPT_NAME, HCP.SF_LSHR_NO_GET(HCP.SF_LSHR_NO_GET(EMPCODE, 9), 8) AS TOEMAIL, HCP.SF_LSHR_NO_GET(HCP.SF_LSHR_NO_GET(EMPCODE, 10), 8) || ';' || HCP.SF_LSHR_NO_GET(HCP.SF_LSHR_NO_GET(EMPCODE, 11), 8) AS CCEMAIL, '' AS TEMP_COMPANY FROM attendance_filtered JOIN tz_opr A ON FGT08 = A.MEANING WHERE rank = 1 -- 取每个员工每个打卡类型的最早记录 ORDER BY status, FGT02; -- 按打卡类型和时间排序

row_number() over(partition by 里面能写case when m