MySQL 查询 loans、payments 和 clients 的剩余金额

2026-10-10支付老李

结论

使用 LEFT JOIN 保留没有付款记录的贷款，再用 COALESCE() 将付款合计中的 NULL 转成 0，并按贷款分组：

SELECT
    l.id_loan,
    c.name AS client,
    l.date_loan,
    l.amount_loan,
    l.amount_loan - COALESCE(SUM(p.amount_payment), 0) AS amount_left
FROM loans AS l
JOIN clients AS c
    ON c.id_client = l.id_client
LEFT JOIN payments AS p
    ON p.id_loan = l.id_loan
GROUP BY
    l.id_loan,
    c.name,
    l.date_loan,
    l.amount_loan
ORDER BY
    l.id_loan;

查询结果如下：

+---------+---------+------------+-------------+-------------+
| id_loan | client  | date_loan  | amount_loan | amount_left |
+---------+---------+------------+-------------+-------------+
| 1       | Robert  | 2020-07-01 | 3000000     | 2450000     |
| 2       | Michael | 2021-07-12 | 2000000     | 2000000     |
+---------+---------+------------+-------------+-------------+

原因

原查询中的 LEFT JOIN payments 已经可以保留没有付款记录的贷款。问题出在 SUM() 上。当某笔贷款没有对应的 payments 记录时，SUM(p.amount_payment) 返回的是 NULL。

在 SQL 中，任何数值减去 NULL，结果还是 NULL：

2000000 - NULL

因此需要写成：

COALESCE(SUM(p.amount_payment), 0)

如果付款合计是 NULL，COALESCE() 会按 0 处理。Michael 的剩余金额就会变成：

2000000 - 0 = 2000000

查询中使用了 SUM()，因此还要按每笔贷款分组。显式列出所有未聚合字段，也可以兼容启用了 ONLY_FULL_GROUP_BY 的 MySQL 配置。

更稳妥的预汇总写法

如果后面还要关联其他一对多表，可以先汇总付款，再与贷款表关联，以免多个一对多关联导致付款金额被重复计算：

SELECT
    l.id_loan,
    c.name AS client,
    l.date_loan,
    l.amount_loan,
    l.amount_loan - COALESCE(p.total_payment, 0) AS amount_left
FROM loans AS l
JOIN clients AS c
    ON c.id_client = l.id_client
LEFT JOIN (
    SELECT
        id_loan,
        SUM(amount_payment) AS total_payment
    FROM payments
    GROUP BY id_loan
) AS p
    ON p.id_loan = l.id_loan
ORDER BY
    l.id_loan;

子查询先把每笔贷款的付款汇总为一行。没有付款记录的贷款仍会被 LEFT JOIN 保留，COALESCE() 则把缺失的付款合计转成 0。

注意事项

如果业务允许贷款关联的客户已经不存在，但仍要显示这笔贷款，那么 clients 也应该使用 LEFT JOIN：

LEFT JOIN clients AS c
    ON c.id_client = l.id_client

如果每笔贷款都必须属于一个有效客户，使用普通的 JOIN 更符合这种数据关系。

不要把针对 payments 表的筛选条件直接放在 WHERE 中。例如：

WHERE p.date_payment >= '2021-01-01'

这个条件会过滤掉 p 为 NULL 的行，让 LEFT JOIN 实际产生接近 INNER JOIN 的效果。如果只想汇总符合条件的付款，应把筛选条件放进关联条件或付款汇总子查询中。