如何将单条记录拆分为多条记录：数据表转日志表实现方案

云上老周

2026-7-23

将datatable数据转换为logtable日志记录

表结构定义

datatable（业务数据表）

CREATE TABLE datatable
(
   "DT_ID"                NUMBER (10, 0),
   "ASSIGNED"             NUMBER (1, 0) DEFAULT 0,
   "ASSIGNED_DATE_TIME"   DATE,
   "ASSIGNED_BY"          VARCHAR2 (7 BYTE),
   "ACCEPTED"             NUMBER (1, 0) DEFAULT 0,
   "ACCEPTED_DATE_TIME"   DATE,
   "ACCEPTED_BY"          VARCHAR2 (7 BYTE),
   "REJECTED"             NUMBER (1, 0) DEFAULT 0,
   "REJECTED_DATE_TIME"   DATE,
   "REJECTED_BY"          VARCHAR2 (7 BYTE)
);

logtable（操作日志表）

CREATE TABLE logtable
(
   dt_id            NUMBER (10, 0),      -- 关联datatable的DT_ID
   "ACTION_OWNER"   NVARCHAR2 (50),      -- 对应当前操作的执行人（如ASSIGNED_BY）
   "ACTION"         VARCHAR2 (50 BYTE),  -- 操作类型：ASSIGNED/REJECTED/ACCEPTED
   "ACTION_DATE"    TIMESTAMP (3),       -- 操作时间（对应各操作的DATE字段）
   "REMARK"         VARCHAR2 (200 BYTE)
);

数据转换SQL

使用UNION ALL将datatable单条记录拆分为多条日志记录，仅当对应操作状态为1（表示已执行该操作）时生成日志：

INSERT INTO logtable (dt_id, "ACTION_OWNER", "ACTION", "ACTION_DATE", "REMARK")
SELECT 
    "DT_ID",
    "ASSIGNED_BY",
    'ASSIGNED' AS "ACTION",
    CAST("ASSIGNED_DATE_TIME" AS TIMESTAMP(3)) AS "ACTION_DATE",
    NULL AS "REMARK"
FROM datatable
WHERE "ASSIGNED" = 1

UNION ALL

SELECT 
    "DT_ID",
    "ACCEPTED_BY",
    'ACCEPTED' AS "ACTION",
    CAST("ACCEPTED_DATE_TIME" AS TIMESTAMP(3)) AS "ACTION_DATE",
    NULL AS "REMARK"
FROM datatable
WHERE "ACCEPTED" = 1

UNION ALL

SELECT 
    "DT_ID",
    "REJECTED_BY",
    'REJECTED' AS "ACTION",
    CAST("REJECTED_DATE_TIME" AS TIMESTAMP(3)) AS "ACTION_DATE",
    NULL AS "REMARK"
FROM datatable
WHERE "REJECTED" = 1;