如何在MySQL中计算每个产品批次的现有库存数量

嘿，我明白你要做的是按每个入库批次（type=1的记录）来计算剩余库存，关联对应的出库记录（type=0且detail_id指向入库id）对吧？结合你给出的示例结果，我整理了一个可行的SQL方案：

首先，假设你的交易明细表名为transaction_details（记得替换成你实际的表名），我们可以通过左连接+分组聚合来实现需求：

SELECT 
    i.id,
    i.item,
    (i.quantity - COALESCE(SUM(o.quantity), 0)) AS sum_quantity,
    -- 生成你需要的计算过程字符串
    CASE 
        WHEN SUM(o.quantity) IS NULL THEN CONCAT(i.quantity)
        ELSE CONCAT(i.quantity, ' - (', GROUP_CONCAT(o.quantity SEPARATOR '+'), ')')
    END AS calculation
FROM 
    transaction_details i
LEFT JOIN 
    transaction_details o 
    ON i.id = o.detail_id 
    AND o.type = 0 -- 只关联出库记录
WHERE 
    i.type = 1 -- 只处理入库批次
GROUP BY 
    i.id, i.item, i.quantity;

代码逻辑解释： #

• 主表筛选：通过WHERE i.type = 1取出所有入库批次，作为我们计算的基础。
• 关联出库记录：用LEFT JOIN关联所有指向该入库批次的出库记录（o.detail_id = i.id且o.type=0），这样即使某个入库没有对应的出库，也能保留该记录。
• 剩余量计算：用入库数量减去出库数量的总和，COALESCE(SUM(o.quantity), 0)用来处理没有出库的情况，避免出现NULL值。
• 计算过程字符串：通过CASE和GROUP_CONCAT生成你需要的格式，比如10 - (5+2)、5 - (5)或者20。

示例结果： #

假设你的原始数据如下：

执行上面的SQL后，会得到你想要的结果：

如果你的需求是按item汇总而不是按入库批次，只要调整分组条件即可，但从你的示例结果来看，应该是按每个入库批次计算剩余量，所以上面的方案应该完全符合你的要求。

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