MySQL使用变量配合SUM聚合计算贷款余额的实现方法

原有SQL无法正常执行的核心原因有两点：

• 同一层级SELECT子句中定义的列别名，无法在当前SELECT层级直接被引用做计算，这是SQL标准执行顺序决定的
• 原写法通过两个独立关联子查询分别统计放款、还款金额，需要对目标数据表扫描2次，存在不必要的性能损耗

通用兼容方案（全SQL版本支持，性能最优） #

通过条件聚合一次性完成两类金额的统计，仅扫描1次目标数据，外层嵌套一层查询即可直接引用聚合结果计算余额：

SELECT
  loanAmount,
  amountPaid,
  (loanAmount - amountPaid) AS balance
FROM (
  SELECT
    COALESCE(SUM(CASE WHEN transactionType = 'DR' THEN amount ELSE 0 END), 0) AS loanAmount,
    COALESCE(SUM(CASE WHEN transactionType = 'CR' THEN amount ELSE 0 END), 0) AS amountPaid
  FROM `loanRepayment`
  WHERE loanNumber = 'MMSE22062311'
) t;

代码中COALESCE函数用于处理无对应交易记录时SUM返回NULL的问题，保证无记录时默认按0计算，避免余额计算结果为NULL的异常。

高版本SQL环境可选方案（CTE写法） #

如果使用MySQL 8.0+、PostgreSQL、SQL Server等支持公用表表达式（CTE）的数据库，可以用CTE写法拆分逻辑，可读性更强：

WITH loan_stat AS (
  SELECT
    COALESCE(SUM(CASE WHEN transactionType = 'DR' THEN amount ELSE 0 END), 0) AS loanAmount,
    COALESCE(SUM(CASE WHEN transactionType = 'CR' THEN amount ELSE 0 END), 0) AS amountPaid
  FROM `loanRepayment`
  WHERE loanNumber = 'MMSE22062311'
)
SELECT
  loanAmount,
  amountPaid,
  (loanAmount - amountPaid) AS balance
FROM loan_stat;

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