如何使用带条件的JOIN/内连接/外连接？获取指定到期日的活跃贷款

先修正原查询里的关联逻辑错误（原查询错误将客户状态关联到贷款表的状态ID），再结合m_loan_repayment_schedule表的分期到期日信息，编写符合需求的查询：

SELECT
    L.account_no AS "LoanID",
    m_client.id AS "ClientID",
    m_client.FULLNAME AS "NAME",
    m_client.FAMILYNO,
    m_client.currentaddresshierarchyid,
    m_client.guardianname AS "Father/Husband Name",
    o.name AS "Branch Name",
    L.loan_status_id AS "LOAN STATUS",
    rs.last_duedate -- 可选：显示最后一期到期日，方便验证结果
FROM m_client
INNER JOIN m_loan L ON m_client.account_no = L.id
INNER JOIN m_office O ON m_client.office_id = O.id
-- 子查询获取每笔贷款的最后一期到期日（兼容12/15/18期任意分期数）
LEFT JOIN (
    SELECT loan_id, MAX(duedate) AS last_duedate
    FROM m_loan_repayment_schedule
    GROUP BY loan_id
) rs ON L.id = rs.loan_id
WHERE
    -- 条件1：贷款状态ID为300（对应2022-08-31到期的活跃贷款）
    L.loan_status_id = '300'
    OR
    -- 条件2：最后一期到期日为2022-08-31，且当前贷款处于活跃状态
    (rs.last_duedate = '2022-08-31' AND L.loan_status_id = '100') -- 请将'100'替换为你系统中实际代表「活跃」的状态ID

关键说明： #

• 子查询rs通过按loan_id分组取MAX(duedate)，精准定位每笔贷款的最后一期到期日，不受分期数影响
• 原查询中关联m_loan S的逻辑错误，改为直接使用m_loan表自身的loan_status_id字段；如果你的系统有独立的贷款状态表（比如m_loan_status），可以补充关联：INNER JOIN m_loan_status s ON L.loan_status_id = s.id
• 条件里的活跃状态ID需替换为你系统中的实际值，比如部分系统用200代表活跃状态，按需调整即可

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