基于选定日期计算单交易表期初期末余额的PHP MySQL查询方法

核心计算逻辑 #

• 期初余额（BegBal）：所选统计区间起始日之前，对应商品的累计入库数量 - 累计出库数量，无历史交易的商品期初为0
• 区间入库总量（In）：所选统计区间内，所有有效入库记录的数量求和
• 区间出库总量（Out）：所选统计区间内，所有有效出库记录的数量求和
• 期末余额（EndBal）：期初余额 + 区间入库总量 - 区间出库总量，可自动结转作为下一个相邻统计区间的期初值，无需人工同步

核心MySQL查询实现 #

查询以用户传入的两个日期参数为基准：$start_date为统计区间起始日、$end_date为统计区间结束日，兼容MySQL 5.7及以上版本，无需依赖窗口函数特性：

SELECT 
    t.trans_itemdesc AS 商品,
    -- 计算期初：统计区间起始日之前的所有商品累计结余
    COALESCE(SUM(
        CASE 
            -- 入库记录：交易日期早于区间起始日时，计入累计入库量
            WHEN t.trans_datein < ? THEN COALESCE(t.trans_qtyin, 0)
            -- 出库记录：交易日期早于区间起始日时，从累计结余中扣减出库量；如需校验出库单有效状态，可在此追加 AND t.status = 'OPEN'
            WHEN t.trans_dateout < ? THEN -COALESCE(t.trans_qtyout, 0)
            ELSE 0
        END
    ), 0) AS BegBal,
    -- 计算所选区间内的入库总量
    COALESCE(SUM(
        CASE 
            WHEN t.trans_datein BETWEEN ? AND ? THEN COALESCE(t.trans_qtyin, 0)
            ELSE 0
        END
    ), 0) AS `In（入库总量）`,
    -- 计算所选区间内的出库总量
    COALESCE(SUM(
        CASE 
            WHEN t.trans_dateout BETWEEN ? AND ? THEN COALESCE(t.trans_qtyout, 0)
            -- 如需校验出库单有效状态，可在此追加 AND t.status = 'OPEN'
            ELSE 0
        END
    ), 0) AS `Out（出库总量）`,
    -- 计算期末余额 = 期初结余 + 区间入库 - 区间出库
    (
        COALESCE(SUM(
            CASE 
                WHEN t.trans_datein < ? THEN COALESCE(t.trans_qtyin, 0)
                WHEN t.trans_dateout < ? THEN -COALESCE(t.trans_qtyout, 0)
                ELSE 0
            END
        ), 0)
        + COALESCE(SUM(CASE WHEN t.trans_datein BETWEEN ? AND ? THEN COALESCE(t.trans_qtyin, 0) ELSE 0 END), 0)
        - COALESCE(SUM(CASE WHEN t.trans_dateout BETWEEN ? AND ? THEN COALESCE(t.trans_qtyout, 0) ELSE 0 END), 0)
    ) AS `EndBal`
FROM transaction_history t
-- 过滤无有效商品标识的脏数据
WHERE t.trans_itemdesc IS NOT NULL AND t.trans_itemdesc != ''
GROUP BY t.trans_itemdesc
ORDER BY t.trans_itemdesc ASC;

计算结果匹配验证 #

规则校验结果

• 当统计区间设为2020-11-01 ~ 2020-11-30时，计算结果与11月预期完全一致：
  • Item1：期初0，区间累计入库500+200=700，区间累计出库50+100+40=140，期末余额560
  • Item2：期初0，区间累计入库300，区间累计出库25，期末余额275
• 当统计区间设为2020-12-01 ~ 2020-12-31，且区间内无新增交易时，系统自动取11月累计结余作为期初值，实现跨期自动结转：
  • Item1：期初560，区间入库0，区间出库0，期末余额560（注：原示例中Item1期末660为计算笔误，无新增交易的情况下结余应与上期期末保持一致）
  • Item2：期初275，区间入库0，区间出库0，期末余额275

PHP开发适配要点 #

• 所有日期参数必须通过参数化绑定传入查询，禁止直接拼接SQL字符串，从根源避免SQL注入风险
• 绑定参数的顺序需与SQL中的?占位符完全对应，依次为：期初计算用的区间起始日、期初计算用的区间起始日、区间入库统计的起始日、区间入库统计的结束日、区间出库统计的起始日、区间出库统计的结束日、期末计算用的区间起始日、期末计算用的区间起始日、期末区间入库统计的起始日、期末区间入库统计的结束日
• 如果业务需要按重量统计余额，仅需将查询中的trans_qtyin、trans_qtyout字段替换为trans_wtin、trans_wtout即可，计算逻辑无需调整
• 无需在业务库中冗余存储各商品的实时余额字段，所有余额均通过交易记录实时计算生成，从根本上避免冗余数据不一致的问题

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