MySQL中如何多表多列求和并计算供应商期末余额？

现有4张数据表：

1. 供应商表（Suppliers）

2. 期初余额表（Start Balance）

3. 发票表（Invoices）

4. 退货表（Returns）

计算逻辑：Start Balance + 发票总额 - 退货总额 = End Balance

尝试使用UNION结合JOIN编写的MySQL语句如下：

SELECT   null  , Supplier_name , ID_A  , SUM(Invoice_value) , null ,  null FROM Suppliers          
         inner  JOIN  Invoices 
         ON ID_A = ID_C 
group by ID_A  

UNION ALL

SELECT   null  , Supplier_name , ID_A  , null , SUM(Return_value),  null  FROM Suppliers          
         left  JOIN  Returns 
         ON ID_A = ID_D
         
group by ID_A 

UNION ALL

SELECT   `Start Balance` ,  Supplier_name, ID_A   , null  , null   ,( `Start Balance` + ifnull(SUM(Invoice_value),0) - ifnull(SUM(Return_value),0) )  FROM Suppliers         
        left  JOIN   `Start Balance` 
         ON ID_A = ID_B
         left  JOIN  Invoices 
         ON ID_A = ID_C 
         left  JOIN  Returns 
         ON ID_A = ID_D 
         
         group by ID_A 

执行后结果分散在不同行，且期末余额计算错误，期望得到如下格式的结果：

正确的做法是先对发票表和退货表按供应商ID单独聚合，计算出每个供应商的发票总额和退货总额，再将这些聚合结果与供应商表、期初余额表关联，最后计算期末余额。这样可以避免多表直接连接导致的重复计算问题，同时保证所有数据在同一行展示。

SQL代码如下：

SELECT
    sb.`Start Balance`,
    s.Supplier_name,
    s.ID_A,
    inv.Total_Invoice AS Invoice_value,
    ret.Total_Return AS Return_value,
    -- 按需求处理null场景计算期末余额
    CASE
        WHEN sb.`Start Balance` IS NULL THEN NULL
        ELSE sb.`Start Balance` + IFNULL(inv.Total_Invoice, 0) - IFNULL(ret.Total_Return, 0)
    END AS End_Balance
FROM Suppliers s
LEFT JOIN `Start Balance` sb ON s.ID_A = sb.ID_B
LEFT JOIN (
    -- 聚合每个供应商的有效发票总额
    SELECT ID_C, SUM(Invoice_value) AS Total_Invoice
    FROM Invoices
    WHERE Invoice_value IS NOT NULL
    GROUP BY ID_C
) inv ON s.ID_A = inv.ID_C
LEFT JOIN (
    -- 聚合每个供应商的有效退货总额
    SELECT ID_D, SUM(Return_value) AS Total_Return
    FROM Returns
    WHERE Return_value IS NOT NULL
    GROUP BY ID_D
) ret ON s.ID_A = ret.ID_D
ORDER BY s.ID_A;

代码说明 #

1. 子查询inv：按供应商ID聚合发票表，过滤掉null值后计算每个供应商的发票总额。
2. 子查询ret：按供应商ID聚合退货表，过滤掉null值后计算每个供应商的退货总额。
3. 主查询：通过左连接关联所有表，确保所有供应商都被包含，不会因为无发票/退货数据被排除。
4. 期末余额计算：使用CASE语句处理期初余额为null的场景，直接返回null；其余情况按公式计算，用IFNULL处理无发票/退货的情况。

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