如何用WITH子句合并trusted_device_details与USER_AUDIT_TRAIL表数据？

根据需求，以下是通过WITH子句合并trusted_device_details和USER_AUDIT_TRAIL表的SQL语句，满足USER_AUDIT_TRAIL按client_code过滤、两表通过user_id关联、trusted_device_details的CLIENT_CODE始终为NULL的要求：

WITH trusted_devices AS (
    -- 处理可信设备表：保留原数据，CLIENT_CODE固定为NULL，只保留与审计表有共同user_id的记录
    SELECT
        DEVICE_TYPE,
        DEVICE_NAME,
        DEVICE_ID,
        "Created Date Time" AS created_dt,
        "Last Accessed Date Time" AS last_accessed_dt,
        IP_ADDRESS,
        USER_ID,
        NULL AS CLIENT_CODE
    FROM trusted_device_details
    WHERE USER_ID IN (SELECT DISTINCT USER_ID FROM USER_AUDIT_TRAIL)
),
audit_trail_devices AS (
    -- 处理审计表：添加CLIENT_CODE过滤条件
    SELECT
        DEVICE_TYPE,
        DEVICE_NAME,
        DEVICE_ID,
        "Created Date Time" AS created_dt,
        "Last Accessed Date Time" AS last_accessed_dt,
        IP_ADDRESS,
        USER_ID,
        CLIENT_CODE
    FROM USER_AUDIT_TRAIL
    -- 替换为你需要的CLIENT_CODE过滤值
    WHERE CLIENT_CODE = '55111444'
)
-- 合并两个数据集，UNION ALL保留所有符合条件的记录（不会自动去重）
SELECT
    DEVICE_TYPE,
    DEVICE_NAME,
    DEVICE_ID,
    created_dt AS "Created Date Time",
    last_accessed_dt AS "Last Accessed Date Time",
    IP_ADDRESS,
    USER_ID,
    CLIENT_CODE
FROM trusted_devices
UNION ALL
SELECT
    DEVICE_TYPE,
    DEVICE_NAME,
    DEVICE_ID,
    created_dt AS "Created Date Time",
    last_accessed_dt AS "Last Accessed Date Time",
    IP_ADDRESS,
    USER_ID,
    CLIENT_CODE
FROM audit_trail_devices
-- 可选：按需求排序
ORDER BY USER_ID, DEVICE_NAME;

关键说明： #

1. WITH子句作用：将两张表的字段输出标准化，避免直接UNION时因字段类型/数量/别名不一致导致的失败
2. 关联逻辑：通过trusted_devices中的WHERE USER_ID IN (SELECT DISTINCT USER_ID FROM USER_AUDIT_TRAIL)实现两表按user_id关联，只保留双方都存在的用户数据
3. 过滤条件：在audit_trail_devices中添加了CLIENT_CODE的过滤，可根据实际需求修改过滤值
4. UNION ALL：使用UNION ALL而非UNION，确保保留所有符合条件的记录（包括两张表中重复的设备记录，与期望结果一致）

对之前尝试的修正： #

你之前的两个查询仅单独处理了单表并做了行号过滤，未进行合并操作。上述SQL通过CTE统一字段后合并，同时满足所有需求。

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