Oracle查询数据表每行最新操作记录并插入新表的实现

首先确保目标表newTable已创建，若未创建可执行以下语句：

CREATE TABLE newTable (
    ID NUMBER(10,0),
    ACTION_USER VARCHAR2(7 BYTE),
    "DATE" DATE
);

方法1：使用GREATEST与CASE表达式 #

通过GREATEST函数找出三个时间字段中的最大值，结合CASE匹配对应的操作人，直接完成插入：

INSERT INTO newTable (ID, ACTION_USER, "DATE")
SELECT
    DT_ID AS ID,
    CASE
        -- 先把NULL时间转为极早日期，避免GREATEST忽略NULL
        WHEN GREATEST(NVL(ASSIGNED_DATE_TIME, TO_DATE('1900-01-01', 'YYYY-MM-DD')),
                      NVL(ACCEPTED_DATE_TIME, TO_DATE('1900-01-01', 'YYYY-MM-DD')),
                      NVL(REJECTED_DATE_TIME, TO_DATE('1900-01-01', 'YYYY-MM-DD'))) = NVL(ASSIGNED_DATE_TIME, TO_DATE('1900-01-01', 'YYYY-MM-DD'))
            THEN ASSIGNED_BY
        WHEN GREATEST(NVL(ASSIGNED_DATE_TIME, TO_DATE('1900-01-01', 'YYYY-MM-DD')),
                      NVL(ACCEPTED_DATE_TIME, TO_DATE('1900-01-01', 'YYYY-MM-DD')),
                      NVL(REJECTED_DATE_TIME, TO_DATE('1900-01-01', 'YYYY-MM-DD'))) = NVL(ACCEPTED_DATE_TIME, TO_DATE('1900-01-01', 'YYYY-MM-DD'))
            THEN ACCEPTED_BY
        ELSE REJECTED_BY
    END AS ACTION_USER,
    GREATEST(NVL(ASSIGNED_DATE_TIME, TO_DATE('1900-01-01', 'YYYY-MM-DD')),
             NVL(ACCEPTED_DATE_TIME, TO_DATE('1900-01-01', 'YYYY-MM-DD')),
             NVL(REJECTED_DATE_TIME, TO_DATE('1900-01-01', 'YYYY-MM-DD'))) AS "DATE"
FROM datatable;

方法2：使用UNPIVOT转换数据结构 #

将每行的三个操作记录转换为多行数据，再通过分组排序筛选出最新操作，逻辑更直观且易于扩展：

INSERT INTO newTable (ID, ACTION_USER, "DATE")
SELECT DT_ID AS ID, ACTION_USER, "DATE"
FROM (
    SELECT
        DT_ID,
        ACTION_USER,
        "DATE",
        -- 按DT_ID分组，操作时间降序排序，标记最新记录
        ROW_NUMBER() OVER (PARTITION BY DT_ID ORDER BY "DATE" DESC) AS rn
    FROM datatable
    -- 横向转纵向，把三个操作字段转为多条记录
    UNPIVOT (
        ("DATE", ACTION_USER) FOR ACTION_TYPE IN (
            (ASSIGNED_DATE_TIME, ASSIGNED_BY) AS 'ASSIGNED',
            (ACCEPTED_DATE_TIME, ACCEPTED_BY) AS 'ACCEPTED',
            (REJECTED_DATE_TIME, REJECTED_BY) AS 'REJECTED'
        )
    )
    WHERE "DATE" IS NOT NULL  -- 过滤无操作时间的无效记录
)
WHERE rn = 1;  -- 只取每组中最新的那条记录

内容的提问来源于stack exchange，提问作者coder11 b