优化包含多子查询的复杂SQL LEFT JOIN语句

看起来你这段SQL确实有点绕，嵌套了好几层子查询，不仅读起来费劲，数据库执行的时候也可能因为重复计算和关联子查询拖慢速度。我来帮你拆解重构一下，让它既清晰又高效。

首先先理清楚你这段逻辑的核心需求：

• 关联租户（A.HTENANT）对应的符合条件的YARDI_COMMAMENDMENTS记录
• 过滤条件：排除ITYPE为4/6/9/11、只保留ISTATUS为1/2的记录，且DTSTART不早于主表的DTSTART
• 还要关联YARDI_UNITXREF，确保COMMAMENDMENTS和UNIT有对应关系
• 最终要取每个租户对应的最新日期的那条记录（用COALESCE(DTMOVEOUT, DTEND, CJ.VAR_DTDATE)作为日期判断）

优化后的SQL写法 #

我用CTE（公共表表达式）把复杂逻辑拆成一个个清晰的步骤，替换掉低效的关联子查询，同时用窗口函数简化取最新记录的逻辑：

WITH Main_CTE AS (
    -- 先把主表A和CJ表关联好，统一管理需要用到的VAR_DTDATE字段
    SELECT 
        A.*,
        CJ.VAR_DTDATE
    FROM 你的主表 A
    -- 这里替换成你原查询中A和CJ的实际关联条件
    JOIN 你的CJ表 CJ ON A.xxxx = CJ.xxxx
),
Filtered_Amendments AS (
    -- 第一步：筛选出符合基础条件的COMMAMENDMENTS，同时关联UNITXREF
    SELECT 
        alu.HMY,
        alu.HTENANT,
        alu.DTSTART,
        alu.DTMOVEOUT,
        alu.DTEND,
        -- 计算用于判断最新记录的日期
        COALESCE(alu.DTMOVEOUT, alu.DTEND, mc.VAR_DTDATE) AS ALU_DATE,
        ux.HUNIT
    FROM ODS.YARDI_COMMAMENDMENTS alu
    -- 把原查询的IN子查询替换成JOIN，性能更优
    JOIN ODS.YARDI_UNITXREF ux 
        ON alu.HMY = ux.HAMENDMENT
    -- 关联主表的租户和DTSTART条件
    JOIN Main_CTE mc
        ON alu.HTENANT = mc.HTENANT
        AND mc.DTSTART <= alu.DTSTART
    WHERE
        alu.ITYPE NOT IN (4, 6, 9, 11)
        AND alu.ISTATUS IN (1, 2)
),
Ranked_Amendments AS (
    -- 第二步：给每个租户的符合条件记录按日期倒序排名，最新的排第1
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY fa.HTENANT 
            ORDER BY fa.ALU_DATE DESC
        ) AS rn
    FROM Filtered_Amendments fa
)
-- 主查询：只关联每个租户的最新那条记录
SELECT 
    mc.*,
    -- 按需选择需要的字段，别用*，减少冗余数据
    ra.HMY, ra.ALU_DATE, ra.HUNIT
FROM Main_CTE mc
LEFT JOIN Ranked_Amendments ra 
    ON mc.HTENANT = ra.HTENANT
    AND ra.rn = 1 -- 只取排名第一的最新记录

优化点说明 #

1. 替换IN子查询为JOIN：原查询里的ALU.HMY IN (SELECT UX2.HAMENDMENT...)改成直接JOIN YARDI_UNITXREF，数据库能更好地优化关联逻辑，避免重复执行子查询拖慢速度。
2. 用窗口函数替代嵌套MAX子查询：原查询嵌套了三层子查询取最大日期，换成ROW_NUMBER()窗口函数后，一次扫描就能完成分组排序，直接取排名第一的就是最新记录，可读性和性能都提升不少。
3. 用CTE拆分逻辑：把主表关联、条件过滤、排名拆分到不同CTE里，每一步的职责清晰，后期维护或者修改条件的时候，不用在大段SQL里找来找去，别人读代码也能快速理解。

额外性能建议 #

• 给YARDI_COMMAMENDMENTS建复合索引：(HTENANT, DTSTART, ITYPE, ISTATUS)，数据库可以快速过滤出符合条件的记录，不用全表扫描。
• 给YARDI_UNITXREF建复合索引：(HAMENDMENT, HUNIT)，加快和COMMAMENDMENTS的关联速度。
• 绝对别用SELECT *，明确写出需要的字段，减少数据传输和内存占用，也避免字段变更带来的意外问题。

如果你的原查询里还有没写完的部分（比如你贴的代码里SELECT UX3....没写完），按照这个思路补全就行，核心就是尽量把嵌套子查询换成JOIN和窗口函数，拆分逻辑让SQL更清爽。

备注：内容来源于stack exchange，提问作者KKU