Firebird 3中SQL查询计算供应商欠款结果异常，如何修正？

场景说明 #

使用Firebird 3，涉及三张业务表：

• seller表：存储供应商基础信息，字段为seller_id（供应商ID）、seller（供应商名称）
• Docs表：存储货物入库明细，字段为doc_id（入库单ID）、doc_summa（入库金额）、seller_id（关联供应商ID）
• paym表：存储支付子明细，字段为paym_id（支付记录ID）、doc_id（关联入库单ID）、paid（支付金额）

期望结果 #

计算各供应商的欠款（入库总金额-已支付金额），目标输出如下：

错误SQL及结果 #

执行以下SQL后，入库总金额被重复计算，得到错误结果：

SELECT
 s.seller_id, 
 sum(d.doc_summa) as summa,
 sum(p.paid) as paid,
 sum(d.doc_summa)-sum(p.paid) as debt

FROM seller s Left Join Docs d  on s.seller_id=d.seller_id
             Left Join  paym p  on p.doc_id= d.doc_id

GROUP BY s.seller_id

错误输出：

问题原因 #

核心问题是多表连接产生笛卡尔积导致重复统计：
当一张入库单（Docs表记录）对应多条支付记录（paym表记录）时，直接关联两张表会让该入库单的金额被重复计算，重复次数等于该入库单对应的支付记录数。比如供应商8的某张1000元入库单若对应2条支付记录，连接后这条入库单会被输出2次，SUM(d.doc_summa)就会计算为2000，而非实际的1000。

修正方案 #

需要先对关联表做聚合处理，避免笛卡尔积，以下提供两种可行写法：

写法1：先分别按供应商聚合入库和支付数据 #

SELECT
    s.seller_id,
    COALESCE(d.total_summa, 0) AS summa,
    COALESCE(p.total_paid, 0) AS paid,
    COALESCE(d.total_summa, 0) - COALESCE(p.total_paid, 0) AS debt
FROM seller s
LEFT JOIN (
    SELECT seller_id, SUM(doc_summa) AS total_summa
    FROM Docs
    GROUP BY seller_id
) d ON s.seller_id = d.seller_id
LEFT JOIN (
    SELECT d.seller_id, SUM(p.paid) AS total_paid
    FROM paym p
    JOIN Docs d ON p.doc_id = d.doc_id
    GROUP BY d.seller_id
) p ON s.seller_id = p.seller_id
GROUP BY s.seller_id, d.total_summa, p.total_paid

写法2：先按入库单聚合支付数据，再关联计算 #

SELECT
    s.seller_id,
    SUM(d.doc_summa) AS summa,
    COALESCE(SUM(p.total_paid), 0) AS paid,
    SUM(d.doc_summa) - COALESCE(SUM(p.total_paid), 0) AS debt
FROM seller s
LEFT JOIN Docs d ON s.seller_id = d.seller_id
LEFT JOIN (
    SELECT doc_id, SUM(paid) AS total_paid
    FROM paym
    GROUP BY doc_id
) p ON d.doc_id = p.doc_id
GROUP BY s.seller_id

两种写法都是先消除一对多关系带来的重复数据，再进行统计计算，确保入库金额和支付金额的统计准确。

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