SQL Server多贷款表关联查询：缺失记录从备用表获取

阿华AIGC实验室

2026-5-26

解决方案：优先从TRN表取数，fallback到LOAN表的统一SQL查询

嘿，我来帮你搞定这个SQL查询的需求！核心思路是用COALESCE()函数实现“优先取TRN表数据，无则用LOAN表”的逻辑，再结合JOIN或UNION来处理多类贷款表的场景。下面分两种常见业务场景给出完整方案：

场景1：合并D类和F类贷款数据为一个结果集

如果你需要把D类（DL_TRN/D_Loan）和F类（FL_TRN/F_LOAN）的贷款记录合并成统一列表，可以用FULL OUTER JOIN处理单类表的fallback，再用UNION ALL合并两类数据：

-- 处理D类贷款：优先DL_TRN，无记录则取D_Loan
SELECT 
    COALESCE(trn.loan_id, loan.loan_id) AS loan_id,
    COALESCE(trn.customer_id, loan.customer_id) AS customer_id,
    COALESCE(trn.loan_amount, loan.loan_amount) AS loan_amount,
    COALESCE(trn.issue_date, loan.issue_date) AS issue_date,
    -- 其他需要的字段都按这个格式用COALESCE处理
    'D_TYPE' AS loan_category
FROM DL_TRN trn
FULL OUTER JOIN D_Loan loan 
    ON trn.loan_id = loan.loan_id

UNION ALL

-- 处理F类贷款：优先FL_TRN，无记录则取F_LOAN
SELECT 
    COALESCE(trn.loan_id, loan.loan_id) AS loan_id,
    COALESCE(trn.customer_id, loan.customer_id) AS customer_id,
    COALESCE(trn.loan_amount, loan.loan_amount) AS loan_amount,
    COALESCE(trn.issue_date, loan.issue_date) AS issue_date,
    -- 其他需要的字段同理
    'F_TYPE' AS loan_category
FROM FL_TRN trn
FULL OUTER JOIN F_LOAN loan 
    ON trn.loan_id = loan.loan_id;

关键逻辑说明：

FULL OUTER JOIN：确保不管TRN表有没有对应记录，都能获取到LOAN表的数据，避免遗漏仅存于LOAN表的记录；
COALESCE()：按顺序返回第一个非NULL的字段值，完美实现“优先TRN， fallback到LOAN”的需求；
loan_category：手动标记数据属于D类还是F类，方便后续区分统计。

场景2：关联D类和F类贷款数据（比如同一客户的两类贷款）

如果你需要把同一客户的D类和F类贷款记录关联展示，可以先分别预处理两类数据的fallback逻辑，再通过关联键（比如customer_id）合并：

SELECT 
    -- D类贷款字段（已处理fallback）
    d_data.loan_id AS d_loan_id,
    d_data.customer_id,
    d_data.loan_amount AS d_loan_amount,
    -- F类贷款字段（已处理fallback）
    f_data.loan_id AS f_loan_id,
    f_data.loan_amount AS f_loan_amount
FROM 
    -- 预处理D类数据：优先DL_TRN，无则取D_Loan
    (SELECT 
         COALESCE(trn.loan_id, loan.loan_id) AS loan_id,
         COALESCE(trn.customer_id, loan.customer_id) AS customer_id,
         COALESCE(trn.loan_amount, loan.loan_amount) AS loan_amount
     FROM DL_TRN trn
     FULL OUTER JOIN D_Loan loan 
         ON trn.loan_id = loan.loan_id) d_data
-- 根据业务关联键（这里用customer_id）关联F类预处理数据
LEFT JOIN 
    -- 预处理F类数据：优先FL_TRN，无则取F_LOAN
    (SELECT 
         COALESCE(trn.loan_id, loan.loan_id) AS loan_id,
         COALESCE(trn.customer_id, loan.customer_id) AS customer_id,
         COALESCE(trn.loan_amount, loan.loan_amount) AS loan_amount
     FROM FL_TRN trn
     FULL OUTER JOIN F_LOAN loan 
         ON trn.loan_id = loan.loan_id) f_data
ON d_data.customer_id = f_data.customer_id;

额外注意事项：

如果你的数据库不支持FULL OUTER JOIN（比如MySQL），可以用LEFT JOIN + UNION来模拟，示例如下：

SELECT trn.* FROM DL_TRN trn
LEFT JOIN D_Loan loan ON trn.loan_id = loan.loan_id
UNION
SELECT loan.* FROM D_Loan loan
LEFT JOIN DL_TRN trn ON trn.loan_id = loan.loan_id
WHERE trn.loan_id IS NULL;