Oracle Apex主表关联多Lookup表，ServiceNow增量同步时间字段问题

我在ServiceNow中对接外部Oracle Apex数据库，要从关联21个Lookup表的主表取数。每个Lookup表都有自己的modified datetime字段，但Lookup表数据变更不会同步更新主表的修改时间。ServiceNow的JDBC数据源要求用单个日期时间字段做增量查询，不能用多字段，否则只能全量同步，不符合最佳实践。

我原本想给每条主表记录计算关联Lookup表的最新修改时间作为这条记录的真实更新时间，但写的SQL返回的是所有Lookup表的全局最新时间，不是每条主表记录对应的最新时间：

SELECT
    main_view.*,
    COALESCE(
        (SELECT MAX(update_datetime) FROM table1),
        (SELECT MAX(update_datetime) FROM table2),
        -- Add more subqueries for the remaining lookup tables...
        (SELECT MAX(update_datetime) FROM table21)
    ) AS most_recent_update_datetime
FROM
    your_existing_view main_view;

所有记录的时间都是06-APR-2023，不是每条记录对应不同的时间。我可以修改主视图的查询语句，但这超出了我的SQL经验。

正确SQL写法（关联每条主表记录的Lookup表） #

你需要把每个Lookup表和主表做关联，只取当前主表记录对应的Lookup记录的最大修改时间，再取这些时间里的最大值。示例如下（假设主表和Lookup表通过main_id关联）：

SELECT
    mv.*,
    GREATEST(
        -- 主表自身的修改时间（如果有的话）
        mv.main_modified_datetime,
        -- 每个Lookup表关联当前主表记录的最新修改时间，没有则用主表时间或NULL
        NVL((SELECT MAX(l1.modified_datetime) FROM table1 l1 WHERE l1.main_id = mv.id), mv.main_modified_datetime),
        NVL((SELECT MAX(l2.modified_datetime) FROM table2 l2 WHERE l2.main_id = mv.id), mv.main_modified_datetime),
        -- 依次添加剩下19个Lookup表的子查询，格式同上
        NVL((SELECT MAX(l21.modified_datetime) FROM table21 l21 WHERE l21.main_id = mv.id), mv.main_modified_datetime)
    ) AS most_recent_update_datetime
FROM
    your_existing_view mv;

关键说明 #

• 必须加关联条件：原来的子查询没关联主表记录，取的是整个Lookup表的全局最大值；现在每个子查询都加了WHERE lX.main_id = mv.id，只针对当前主表记录对应的Lookup数据计算最新时间。
• 用GREATEST替代COALESCE：COALESCE是取第一个非NULL值，GREATEST是取所有值里的最大值，更符合需求；如果主表没有自身修改时间，也可以把第一个参数换成NULL，再用NVL兜底。
• 处理NULL情况：用NVL确保如果某个Lookup表没有对应记录，不会返回NULL影响GREATEST计算，直接用主表的修改时间替代。

优化建议（可选） #

如果21个子查询性能太差，可以改成JOIN+MAX的方式，先对每个Lookup表按主表ID分组取最大修改时间，再合并后和主表关联：

WITH lookup_updates AS (
    -- 合并所有Lookup表的最新修改时间
    SELECT main_id, MAX(modified_datetime) AS lookup_max FROM table1 GROUP BY main_id
    UNION ALL
    SELECT main_id, MAX(modified_datetime) AS lookup_max FROM table2 GROUP BY main_id
    -- 添加剩下19个Lookup表的UNION ALL
    UNION ALL
    SELECT main_id, MAX(modified_datetime) AS lookup_max FROM table21 GROUP BY main_id
),
per_main_updates AS (
    -- 按主表ID取所有关联Lookup的最大时间
    SELECT main_id, MAX(lookup_max) AS overall_lookup_max FROM lookup_updates GROUP BY main_id
)
SELECT
    mv.*,
    GREATEST(mv.main_modified_datetime, NVL(pmu.overall_lookup_max, mv.main_modified_datetime)) AS most_recent_update_datetime
FROM your_existing_view mv
LEFT JOIN per_main_updates pmu ON pmu.main_id = mv.id;

这种方式性能更好，尤其是当Lookup表数据量很大的时候。

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