Oracle无显式键关联两张表：匹配Rate与Rate_Prev需求

要实现需求，核心是两次关联表2：一次匹配当前周期的维度字段获取Rate，另一次匹配上一周期的维度字段获取Rate_Prev，具体SQL实现如下：

SELECT 
    t1.Case_No,
    t1.Month,
    t1.Month_Prev,
    t1.Code,
    t1.Stage,
    t1.Code_Prev,
    t1.Stage_Prev,
    t1.Status,
    t2_current.Rate,
    t2_prev.Rate AS Rate_Prev
FROM 
    表1 t1
LEFT JOIN 
    表2 t2_current 
    ON t1.Month = t2_current.Month
    AND t1.Code = t2_current.Code
    AND t1.Stage = t2_current.Stage
    AND t1.Status = t2_current.Status
LEFT JOIN 
    表2 t2_prev 
    ON t1.Month_Prev = t2_prev.Month
    AND t1.Code_Prev = t2_prev.Code
    AND t1.Stage_Prev = t2_prev.Stage
    AND t1.Status = t2_prev.Status;

关键说明 #

• 用t2_current和t2_prev作为表2的别名，明确区分两次关联的逻辑
• 使用LEFT JOIN可确保表1的记录即使在表2中无对应匹配时也能保留，此时对应的Rate或Rate_Prev会显示为NULL；若业务要求必须有匹配记录才保留，可替换为INNER JOIN
• 关联条件严格对应需求中的维度组合：当前Rate匹配(Month,Code,Stage,Status)，历史Rate匹配(Month_Prev,Code_Prev,Stage_Prev,Status)

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