Oracle SQL 實戰：將考勤打卡記錄拆分為「進廠/出廠」雙欄顯示

需求背景

考勤系統單欄存儲所有打卡時間（進/出交替），報表需拆分為成對的「入廠時間、出廠時間」兩欄，並按人員、日期彙總工時。

測試數據準備

CREATE TABLE CMIS_PERSON_FI_DETAIL_TB (
  detail_id     NUMBER(20) PRIMARY KEY,
  scan_time     DATE,
  person_fk_id  NUMBER(20)
);

-- 模擬張三 2015-06-18 打卡 8 次（4 進 4 出）
INSERT INTO CMIS_PERSON_FI_DETAIL_TB VALUES (1, TO_DATE('2015-06-18 08:00:00','YYYY-MM-DD HH24:MI:SS'), 1001);
INSERT INTO CMIS_PERSON_FI_DETAIL_TB VALUES (2, TO_DATE('2015-06-18 10:30:00','YYYY-MM-DD HH24:MI:SS'), 1001);
INSERT INTO CMIS_PERSON_FI_DETAIL_TB VALUES (3, TO_DATE('2015-06-18 11:00:00','YYYY-MM-DD HH24:MI:SS'), 1001);
INSERT INTO CMIS_PERSON_FI_DETAIL_TB VALUES (4, TO_DATE('2015-06-18 12:30:00','YYYY-MM-DD HH24:MI:SS'), 1001);
INSERT INTO CMIS_PERSON_FI_DETAIL_TB VALUES (5, TO_DATE('2015-06-18 13:30:00','YYYY-MM-DD HH24:MI:SS'), 1001);
INSERT INTO CMIS_PERSON_FI_DETAIL_TB VALUES (6, TO_DATE('2015-06-18 17:30:00','YYYY-MM-DD HH24:MI:SS'), 1001);
INSERT INTO CMIS_PERSON_FI_DETAIL_TB VALUES (7, TO_DATE('2015-06-18 18:00:00','YYYY-MM-DD HH24:MI:SS'), 1001);
INSERT INTO CMIS_PERSON_FI_DETAIL_TB VALUES (8, TO_DATE('2015-06-18 18:30:00','YYYY-MM-DD HH24:MI:SS'), 1001);
COMMIT;

核心 SQL：利用 ROW_NUMBER 分組配對

WITH ordered AS (
  SELECT 
    person_fk_id,
    TRUNC(scan_time) AS work_date,
    scan_time,
    ROW_NUMBER() OVER (PARTITION BY person_fk_id, TRUNC(scan_time) ORDER BY scan_time) AS rn
  FROM CMIS_PERSON_FI_DETAIL_TB
),
paired AS (
  SELECT 
    person_fk_id,
    work_date,
    MAX(CASE WHEN MOD(rn,2)=1 THEN scan_time END) AS in_time,
    MAX(CASE WHEN MOD(rn,2)=0 THEN scan_time END) AS out_time,
    CEIL(rn/2) AS pair_seq
  FROM ordered
  GROUP BY person_fk_id, work_date, CEIL(rn/2)
)
SELECT 
  p.person_fk_id,
  p.work_date,
  p.in_time,
  p.out_time,
  ROUND((p.out_time - p.in_time)*24*60) AS duration_min
FROM paired p
WHERE p.in_time IS NOT NULL AND p.out_time IS NOT NULL
ORDER BY p.person_fk_id, p.work_date, p.pair_seq;

輸出結果示例

PERSON_FK_ID WORK_DATE  IN_TIME           OUT_TIME          DURATION_MIN
------------ ---------- ----------------- ----------------- ------------
        1001 2015-06-18 2015-06-18 08:00 2015-06-18 10:30            150
        1001 2015-06-18 2015-06-18 11:00 2015-06-18 12:30             90
        1001 2015-06-18 2015-06-18 13:30 2015-06-18 17:30            240
        1001 2015-06-18 2015-06-18 18:00 2015-06-18 18:30             30

進階：彙總每日總工時

SELECT 
  person_fk_id,
  work_date,
  SUM(ROUND((out_time - in_time)*24*60)) AS total_minutes,
  TO_CHAR(TRUNC(SUM(out_time - in_time)*24)) || '小時' || 
  TO_CHAR(ROUND(MOD(SUM(out_time - in_time)*24*60,60))) || '分鐘' AS work_hours
FROM paired
WHERE in_time IS NOT NULL AND out_time IS NOT NULL
GROUP BY person_fk_id, work_date
ORDER BY person_fk_id, work_date;

關鍵點解析

1. ROW_NUMBER() 保證同一天內按時間順序編號，奇數行=進場，偶數行=出場。
2. CEIL(rn/2) 將相鄰兩行歸為同一組。
3. 條件聚合 MAX(CASE...) 將同組的奇/偶行轉為兩欄。
4. 若數據存在缺漏（如忘打卡），可加入異常標記邏輯或外連接補全。

此模式廣泛適用於門禁、考勤、物流掃描等「成對事件」拆分場景，純 SQL 實現，無需 PL/SQL 迴圈，效能優異。