SQL查询结果与loan_repayment_schedule表数据不符，如何修正？

问题核心是loan_repayment_schedule表中单个loan_id可能对应多条还款计划记录，直接关联会返回所有匹配记录，或意外取到非目标数据。仅通过匹配loan_id获取正确数据，可采用以下两种修正方式：

方式一：获取每个loan_id的最新还款记录（按更新时间排序） #

如果updated_at代表记录的更新时间，且最新记录为正确数据，可通过窗口函数先筛选出每个loan_id的最新记录，再关联主查询：

SELECT 
    loan_applications.province_id AS province_id,
    loan_applications.branch AS branch,
    loans.id AS loanid,
    loans.client_id,
    loans.loan_application_id,
    loan_applications.disbursement_date AS disbdate,
    loan_applications.disbursed_amount AS disbamt,
    loans.term_period,
    loan_applications.current_loan_place AS loancycle,
    loans.last_repayment_date AS maturitydate,
    clients.dob,
    JSON_EXTRACT(loan_question_data.input_data, '$.gender') AS gender,
    JSON_EXTRACT(loan_question_data.input_data, '$.martial_status') AS martial,
    JSON_EXTRACT(loan_question_data.input_data, '$.education') AS education,
    rps.status,
    rps.remain_balance,
    rps.period,
    rps.repayment_date,
    rps.updated_at
FROM
    loans
JOIN loan_applications ON loans.loan_application_id = loan_applications.id
JOIN clients ON loans.client_id = clients.id
JOIN loan_question_data ON loan_applications.id = loan_question_data.id
JOIN (
    SELECT 
        loan_id,
        status,
        remain_balance,
        period,
        repayment_date,
        updated_at,
        ROW_NUMBER() OVER (PARTITION BY loan_id ORDER BY updated_at DESC) AS rn
    FROM loan_repayment_schedule
) AS rps ON loans.id = rps.loan_id AND rps.rn = 1
WHERE
    loans.created_at BETWEEN '2024-05-01' AND '2024-05-31';

方式二：匹配特定业务规则的还款记录 #

如果已知正确记录的业务标识（比如当前还款周期、特定状态），可在子查询中加入对应筛选条件：

SELECT 
    loan_applications.province_id AS province_id,
    loan_applications.branch AS branch,
    loans.id AS loanid,
    loans.client_id,
    loans.loan_application_id,
    loan_applications.disbursement_date AS disbdate,
    loan_applications.disbursed_amount AS disbamt,
    loans.term_period,
    loan_applications.current_loan_place AS loancycle,
    loans.last_repayment_date AS maturitydate,
    clients.dob,
    JSON_EXTRACT(loan_question_data.input_data, '$.gender') AS gender,
    JSON_EXTRACT(loan_question_data.input_data, '$.martial_status') AS martial,
    JSON_EXTRACT(loan_question_data.input_data, '$.education') AS education,
    rps.status,
    rps.remain_balance,
    rps.period,
    rps.repayment_date,
    rps.updated_at
FROM
    loans
JOIN loan_applications ON loans.loan_application_id = loan_applications.id
JOIN clients ON loans.client_id = clients.id
JOIN loan_question_data ON loan_applications.id = loan_question_data.id
JOIN (
    SELECT * 
    FROM loan_repayment_schedule
    -- 替换为实际业务的筛选条件，比如取最大周期数的记录
    WHERE period = (SELECT MAX(period) FROM loan_repayment_schedule WHERE loan_id = loan_repayment_schedule.loan_id)
) AS rps ON loans.id = rps.loan_id
WHERE
    loans.created_at BETWEEN '2024-05-01' AND '2024-05-31';

补充说明 #

• 方式一通过ROW_NUMBER()窗口函数按loan_id分组，倒序排序后只保留每组第一条，确保拿到最新更新的记录。
• 方式二适用于明确业务规则的场景，直接筛选符合规则的目标记录。

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