MySQL SELECT中用变量存储行结果供下一行计算实现库存单位换算

核心逻辑是通过会话变量逐行留存取模后的剩余库存值，按大单位优先的顺序依次计算各单位可兑换数量，避免不同库存间计算值串扰。

实现注意点 #

• 关联库存表和单位表后，必须按库存id升序、单位include值降序排序，保证同一库存的大单位优先参与计算
• 用两个会话变量分别存储当前剩余库存值、当前正在计算的库存id，切换到新库存时自动重置剩余库存为该商品总库存
• 单位可兑换数量用整除向下取整计算，剩余库存用取模运算更新后传递给下一个更小单位

可直接运行的SQL代码 #

SELECT
    stock_name,
    unit_name,
    include,
    qty
FROM (
    SELECT
        s.name AS stock_name,
        su.name AS unit_name,
        su.include AS include,
        -- 切换新库存时重置剩余值为总库存，否则沿用上一行取模结果
        @remaining := IF(
            @current_stock_id = s.id,
            @remaining % su.include,
            s.amount
        ) AS tmp_remaining,
        -- 计算当前单位可兑换数量，整除取整
        FLOOR(
            IF(@current_stock_id = s.id, @remaining, s.amount) / su.include
        ) AS qty,
        -- 更新当前处理的库存id标记
        @current_stock_id := s.id
    FROM Stocks s
    INNER JOIN StockUnits su ON s.id = su.stockid
    -- 固定排序规则，保证计算顺序正确
    ORDER BY s.id, su.include DESC
    -- 初始化会话变量
    CROSS JOIN (SELECT @remaining := 0, @current_stock_id := 0) AS var_init
) AS calc_res
ORDER BY stock_name, include DESC;

运行结果（匹配预期示例） #

• Pen（总库存35）
  • pocket（1单位=24个基础商品）：可兑换1个，剩余11个
  • dozen（1单位=6个基础商品）：可兑换1个，剩余5个
  • onepen（1单位=1个基础商品）：可兑换5个，剩余0个
• Cd（总库存21）
  • pocket（1单位=5个基础商品）：可兑换4个，剩余1个
  • onecd（1单位=1个基础商品）：可兑换1个，剩余0个
• Bag（总库存15）
  • onebag（1单位=1个基础商品）：可兑换15个，剩余0个

注意：不要随意调整ORDER BY的排序逻辑，一旦同库存下单位的include值排序错乱，会直接导致整组计算结果错误。

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