如何基于EnterpriseCustomerID关联三张不同列的数据库表？

方法1：嵌套子查询写法 #

直接将前两张表的连接结果作为子查询，再与第三张表关联：

SELECT 
    nt.EnterpriseCustomerID,
    nt.RelationshipEstablishDate,
    nt.EstimatedIncome,
    sa.LGD_Rating,
    sa.PD_Rating
FROM (
    -- 前两张表内连接，生成临时结果集
    SELECT 
        sb.EnterpriseCustomerID,
        sb.RelationshipEstablishDate,
        sp.EstimatedIncome
    FROM [DWH_RPT].[dw].[vFlatShareBalance] sb
    INNER JOIN [DWH_RPT].[dw].[vFlatSoftPull] sp 
        ON sb.EnterpriseCustomerID = sp.EnterpriseCustomerID
) nt
INNER JOIN [DWH_RPT].[dw].[vFlatServicingAction] sa 
    ON nt.EnterpriseCustomerID = sa.EnterpriseCustomerID;

方法2：WITH子句（CTE）修正版 #

如果之前用WITH失败，大概率是语法或字段引用错误，以下是正确的CTE写法：

WITH NEW_TABLE AS (
    SELECT 
        sb.EnterpriseCustomerID,
        sb.RelationshipEstablishDate,
        sp.EstimatedIncome
    FROM [DWH_RPT].[dw].[vFlatShareBalance] sb
    INNER JOIN [DWH_RPT].[dw].[vFlatSoftPull] sp 
        ON sb.EnterpriseCustomerID = sp.EnterpriseCustomerID
)
SELECT 
    nt.EnterpriseCustomerID,
    nt.RelationshipEstablishDate,
    nt.EstimatedIncome,
    sa.LGD_Rating,
    sa.PD_Rating
FROM NEW_TABLE nt
INNER JOIN [DWH_RPT].[dw].[vFlatServicingAction] sa 
    ON nt.EnterpriseCustomerID = sa.EnterpriseCustomerID;

常见问题排查 #

• 确认三个表的EnterpriseCustomerID字段类型一致，避免隐式转换导致连接失败
• 检查子查询/CTE中是否存在同名字段冲突，需明确指定表别名（比如两张表都有EstimatedIncome时要指定sp.EstimatedIncome）
• 核对字段名拼写，确保三张表都存在EnterpriseCustomerID、LGD_Rating、PD_Rating等目标字段

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