PostgreSQL多表LEFT JOIN含SUM运算及库存字段计算问题求助

我明白你现在的困扰：直接对这四张表做LEFT JOIN的话，很容易因为usage和product_restock的多条记录导致SUM值被重复计算，而且还要从daily_stock里提取指定日期的库存值作为begin和end stock。下面是具体的解决方案：

核心思路 #

先分别对需要聚合的表（usage、product_restock）按product_id做分组聚合，再把聚合后的结果和product表、daily_stock的日期筛选结果做LEFT JOIN，这样就能避免多对多关联带来的重复计算问题。

完整SQL代码 #

SELECT
    p.id,
    p.product_name,
    ds_begin.qty AS begin_stock,
    COALESCE(r.restock_total, 0) AS restock,
    COALESCE(u.used_total, 0) AS used,
    ds_end.qty AS end_stock
FROM product p
LEFT JOIN (
    SELECT product_id, qty
    FROM daily_stock
    WHERE dates_stat = '2020-12-18'
) ds_begin ON p.id = ds_begin.product_id
LEFT JOIN (
    SELECT product_id, qty
    FROM daily_stock
    WHERE dates_stat = '2020-12-19'
) ds_end ON p.id = ds_end.product_id
LEFT JOIN (
    SELECT product_id, SUM(restock_amount) AS restock_total
    FROM product_restock
    WHERE date_in BETWEEN '2020-12-18' AND '2020-12-19'
    GROUP BY product_id
) r ON p.id = r.product_id
LEFT JOIN (
    SELECT product_id, SUM(used) AS used_total
    FROM usage
    WHERE date_out BETWEEN '2020-12-18' AND '2020-12-19'
    GROUP BY product_id
) u ON p.id = u.product_id
ORDER BY p.id;

代码说明 #

1. daily_stock处理：用两个子查询分别提取2020-12-18（begin_stock）和2020-12-19（end_stock）的库存值，因为这两个是指定日期的最终值，不需要聚合。
2. 聚合子查询：对usage和product_restock分别按product_id分组，计算总使用量和总补货量，同时过滤日期范围。
3. COALESCE函数：用来处理那些没有补货或使用记录的产品，把NULL值替换为0，符合预期结果的显示要求。
4. LEFT JOIN：确保product表中的所有产品都能出现在结果中，即使没有对应的库存、补货或使用记录。

执行这段SQL后，就能得到你预期的结果：

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