单表关联另一表两列的高效SQL左连接优化方案咨询

尝试将表T1与表T2关联，关联条件为T1.Col1 = T2.Col1 或 T1.Col1 = T2.Col2，但该查询耗时超40分钟无法使用。改用COALESCE(T2.Col1, T2.Col2)的关联方式虽速度快，但当T2的Col1和Col2分别在不同行与T1.Col1关联时会丢失数据。需实现T1左连接T2且保留所有关联数据的更优方案，相关信息如下：

当前输出 #

期望输出 #

实际SQL语句 #

SELECT *
FROM dbo.TestGeneralTransYear2022 AS ledger
LEFT OUTER JOIN dbo.SRM_InventTransSettlementTab AS trans 
    ON ledger.SUBLEDGERVOUCHER = COALESCE(trans.VOUCHER COLLATE Polish_CI_AS, trans.voucherphysical COLLATE Polish_CI_AS)

数据表示例 #

方案1：拆分OR关联为双左连接后聚合 #

把原OR条件拆成两个独立的左连接，通过UNION ALL合并所有匹配结果，再按T1的主键分组聚合，确保不会丢失任何关联数据：

WITH combined_matches AS (
    -- 匹配VOUCHER的记录
    SELECT 
        ledger.ID AS t1_id,
        trans.TRANS1,
        trans.TRANS2
    FROM dbo.TestGeneralTransYear2022 AS ledger
    LEFT JOIN dbo.SRM_InventTransSettlementTab AS trans 
        ON ledger.SUBLEDGERVOUCHER = trans.VOUCHER COLLATE Polish_CI_AS
    
    UNION ALL
    
    -- 匹配voucherphysical的记录
    SELECT 
        ledger.ID AS t1_id,
        trans.TRANS1,
        trans.TRANS2
    FROM dbo.TestGeneralTransYear2022 AS ledger
    LEFT JOIN dbo.SRM_InventTransSettlementTab AS trans 
        ON ledger.SUBLEDGERVOUCHER = trans.voucherphysical COLLATE Polish_CI_AS
)
SELECT 
    t1_id AS [T1.ID],
    MAX(TRANS1) AS [T2.TRANS1], -- 用MAX确保取到非空值，可根据实际业务调整聚合逻辑
    MAX(TRANS2) AS [T2.TRANS2]
FROM combined_matches
GROUP BY t1_id;

方案2：预处理T2表统一匹配字段 #

先将T2中的VOUCHER和voucherphysical整理到单个字段中，消除OR条件的性能瓶颈，再与T1关联：

WITH normalized_trans AS (
    SELECT 
        -- 生成统一的匹配值
        CASE 
            WHEN VOUCHER IS NOT NULL THEN VOUCHER COLLATE Polish_CI_AS
            ELSE voucherphysical COLLATE Polish_CI_AS
        END AS match_value,
        TRANS1,
        TRANS2
    FROM dbo.SRM_InventTransSettlementTab
    WHERE COALESCE(VOUCHER, voucherphysical) IS NOT NULL -- 过滤无匹配价值的记录
)
SELECT 
    ledger.ID AS [T1.ID],
    MAX(trans.TRANS1) AS [T2.TRANS1],
    MAX(trans.TRANS2) AS [T2.TRANS2]
FROM dbo.TestGeneralTransYear2022 AS ledger
LEFT JOIN normalized_trans AS trans 
    ON ledger.SUBLEDGERVOUCHER = trans.match_value
GROUP BY ledger.ID;

性能优化要点 #

• 为SRM_InventTransSettlementTab的VOUCHER和voucherphysical字段创建非聚集索引，并包含TRANS1、TRANS2等查询所需字段，避免回表操作。
• 确保索引的排序规则与COLLATE Polish_CI_AS一致，防止隐式转换导致索引失效。
• 替换SELECT *为明确字段列表，减少数据传输和内存消耗。

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