将含子查询的Oracle SQL语句转换为JOIN写法求助

需求：将以下含嵌套子查询的Oracle SQL语句转换为仅使用Oracle JOIN的写法：

SELECT *
  FROM EXPORT_TABLE doe
 WHERE 1 = 1
   AND doe.last_update_date < SYSDATE - 1
   AND ID NOT IN (
                   SELECT ID
                     FROM EXPORT_TABLE
                    WHERE LAST_UPDATE_DATE IN (
                                                SELECT MAX(LAST_UPDATE_DATE)
                                                  FROM EXPORT_TABLE
                                                 GROUP BY DI_EXTRACT_ID
                                              )
                 )

我尝试了以下JOIN写法的语句，但结果与原查询不一致，请求正确的转换方法：

SELECT distinct doe.*
  FROM EXPORT_TABLE doe,
       (SELECT MAX(LAST_UPDATE_DATE)
              ,max(ID) id
          FROM EXPORT_TABLE
         GROUP BY DI_EXTRACT_ID) sort_table
 WHERE 1 = 1
   AND doe.id <> sort_table.id
   AND doe.last_update_date < SYSDATE - 1

问题分析 #

原查询逻辑是筛选出EXPORT_TABLE中满足两个条件的记录：

1. 记录的last_update_date早于当前日期前1天
2. 该记录不属于每个DI_EXTRACT_ID对应最新更新时间的记录集合

你尝试的写法存在两个核心问题：

• 子查询用max(ID)获取ID错误：每个DI_EXTRACT_ID的最新更新时间可能对应多条记录，且ID不一定是最大值，会漏掉部分需要排除的记录
• 隐式笛卡尔积加doe.id <> sort_table.id的过滤逻辑错误：会错误排除不属于当前sort_table行对应DI_EXTRACT_ID但本身属于其他DI_EXTRACT_ID最新记录的行，还可能因重复过滤导致结果偏差

正确的JOIN写法 #

SELECT doe.*
FROM EXPORT_TABLE doe
LEFT JOIN (
    -- 获取每个DI_EXTRACT_ID对应的所有最新更新时间的记录ID
    SELECT et.ID
    FROM EXPORT_TABLE et
    INNER JOIN (
        -- 先计算每个DI_EXTRACT_ID的最大更新时间
        SELECT DI_EXTRACT_ID, MAX(LAST_UPDATE_DATE) AS max_update_date
        FROM EXPORT_TABLE
        GROUP BY DI_EXTRACT_ID
    ) max_dates 
        ON et.DI_EXTRACT_ID = max_dates.DI_EXTRACT_ID 
        AND et.LAST_UPDATE_DATE = max_dates.max_update_date
) latest_records 
    ON doe.ID = latest_records.ID
-- 筛选符合更新时间要求且不属于最新记录集合的行
WHERE doe.last_update_date < SYSDATE - 1
  AND latest_records.ID IS NULL;

写法说明 #

1. 内层子查询max_dates：计算每个DI_EXTRACT_ID对应的最新更新时间
2. 中间子查询latest_records：通过JOIN关联原表，找出所有属于最新更新时间的记录ID
3. 主查询用LEFT JOIN关联原表和latest_records，通过latest_records.ID IS NULL筛选出不在最新记录集合里的行，同时保留last_update_date < SYSDATE -1的过滤条件

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