关联三表计算应收实收差额并过滤零余额的SQL查询需求

表结构说明 #

• accounts表：存储用户信息，核心字段id（用户ID）、name（用户名称）
• receivables表：应收款记录，核心字段id（记录ID）、ref（关联编号）、account_id（关联用户ID）、receivable（应收金额）
• receiveds表：收款记录，核心字段ref（关联编号）、received（收款金额）

需求回顾 #

以receivables为基础表，关联accounts获取用户名称；按ref汇总receiveds的received金额；用receivables.receivable减去该汇总值得到余额，过滤掉余额为0的记录；同时统计每个用户ID的总余额。

常见错误点 #

错误通常出现在未正确分组汇总收款记录，或关联时产生笛卡尔积，导致余额计算失真。

正确查询语句 #

SELECT
    r.id,
    a.name AS user_name,
    r.ref,
    r.receivable,
    COALESCE(SUM(rc.received), 0) AS total_received,
    (r.receivable - COALESCE(SUM(rc.received), 0)) AS balance,
    SUM(r.receivable - COALESCE(SUM(rc.received), 0)) OVER (PARTITION BY r.account_id) AS total_balance_per_id
FROM
    receivables r
LEFT JOIN
    accounts a ON r.account_id = a.id
LEFT JOIN
    receiveds rc ON r.ref = rc.ref
GROUP BY
    r.id, a.name, r.ref, r.receivable, r.account_id
HAVING
    (r.receivable - COALESCE(SUM(rc.received), 0)) != 0
ORDER BY
    r.account_id, r.id;

语句说明 #

1. 左连接保证基础数据完整：用LEFT JOIN保留receivables的所有记录，COALESCE将无收款的情况默认置为0，避免空值干扰计算
2. 精准分组汇总：按receivables单条记录维度分组，同时聚合对应ref的所有收款金额
3. 余额过滤：通过HAVING子句直接过滤余额为0的无效记录
4. 窗口函数计算用户总余额：用SUM() OVER (PARTITION BY r.account_id)按用户ID分组，自动计算该用户所有有效余额的总和

内容的提问来源于stack exchange，提问作者Ahmed Guure