本文详解如何通过mysql cte（公用表表达式）联合多表，精准计算指定日期、指定仓库下各商品的入库量（inward）、出库量（outward）及动态结存（closing），支持按产品+业务类型（p/o）分行列示。
在库存管理类系统中，常需按特定日期和指定仓库维度，汇总每个商品的当日出入库明细及实时结存。本教程提供一套健壮、可复用的MySQL解决方案，基于标准的五表结构（products、purchase、purchase_item、order、order_item），使用现代SQL特性（CTE + UNION ALL + 窗口聚合逻辑）实现清晰、高效的数据透视。
核心设计思路
我们不依赖初始 products.qty 字段（该字段易过期且非事务性），而是以业务单据为源头，动态计算结存：
- Inward（入库） → 来自 purchase_item 关联 purchase 的 qty；
- Outward（出库） → 来自 order_item 关联 order 的 qty；
- Closing（结存） → 按产品分组后，对同一产品的所有 Inward - Outward 累计求和（即：SUM(Inward) - SUM(Outward)）；
- 为区分业务类型，使用 'P' 标识采购入库行，'O' 标识销售出库行，并在产品名后追加 (P) 或 (O)。
⚠️ 注意：原始问题中 order 表字段名为 detail 存储仓库ID，但建表SQL实际定义为 warehouse_id —— 本方案统一采用 warehouse_id 字段，确保语义一致与可维护性。
完整可执行SQL（MySQL 8.0+）
WITH stock_movements AS (
-- 【入库行】：采购单据明细
SELECT
'P' AS movement_type,
p.id AS product_id,
CONCAT(p.product_name, '(P)') AS product_label,
COALESCE(CAST(pi.qty AS DECIMAL(10,2)), 0) AS inward_qty,
0.0 AS outward_qty,
pu.warehouse_id,
pu.`date` AS transaction_date
FROM products p
LEFT JOIN purchase_item pi ON p.id = pi.product_id
LEFT JOIN purchase pu ON pi.purchase_id = pu.id
WHERE pu.`date` IS NOT NULL -- 排除无日期的脏数据
UNION ALL
-- 【出库行】：销售订单明细
SELECT
'O' AS movement_type,
p.id AS product_id,
CONCAT(p.product_name, '(O)') AS product_label,
0.0 AS inward_qty,
COALESCE(CAST(oi.qty AS DECIMAL(10,2)), 0) AS outward_qty,
o.warehouse_id,
o.`date` AS transaction_date
FROM products p
LEFT JOIN order_item oi ON p.id = oi.product_id
LEFT JOIN `order` o ON oi.order_id = o.id
WHERE o.`date` IS NOT NULL
)
SELECT
product_label AS `Product name`,
COALESCE(SUM(CASE WHEN movement_type = 'P' THEN inward_qty END), 0) AS `Inward`,
COALESCE(SUM(CASE WHEN movement_type = 'O' THEN outward_qty END), 0) AS `Outward`,
COALESCE(
SUM(CASE WHEN movement_type = 'P' THEN inward_qty ELSE 0 END)
- SUM(CASE WHEN movement_type = 'O' THEN outward_qty ELSE 0 END),
0
) AS `Closing`
FROM stock_movements
WHERE transaction_date = '2022-03-02' -- ✅ 动态参数：目标日期
AND warehouse_id = 1 -- ✅ 动态参数：目标仓库ID
GROUP BY product_id, product_label
ORDER BY product_id, movement_type;输出结果说明（示例）
| Product name | Inward | Outward | Closing |
|---|---|---|---|
| TEST(P) | 2.00 | 0.00 | 2.00 |
| TEST(O) | 0.00 | 1.00 | 1.00 |
| TEST 2(P) | 3.00 | 0.00 | 3.00 |
| TEST 2(O) | 0.00 | 1.00 | 2.00 |
- 每个商品生成两条记录（X(P) 和 X(O)），便于前端渲染为明细报表；
- Inward/Outward 列仅在对应类型行显示数值，其余为 0（非 NULL），提升可读性与下游兼容性；
- Closing 是严格按 SUM(Inward) - SUM(Outward) 计算，真实反映该商品在指定仓库、指定日期的净库存变动。
关键注意事项与优化建议
- ✅ 数据类型安全：purchase_item.qty 与 order_item.qty 原为 VARCHAR，使用 CAST(... AS DECIMAL) 避免字符串隐式转换错误；
- ✅ 空值鲁棒性：全程使用 COALESCE(..., 0) 处理 NULL，防止聚合中断；
- ⚠️ 性能提示：务必为高频查询字段添加复合索引，例如：
ALTER TABLE purchase ADD INDEX idx_warehouse_date (warehouse_id, `date`); ALTER TABLE `order` ADD INDEX idx_warehouse_date (warehouse_id, `date`); ALTER TABLE purchase_item ADD INDEX idx_purchase_product (purchase_id, product_id); ALTER TABLE order_item ADD INDEX idx_order_product (order_id, product_id);
- ? 扩展性：如需支持“期初库存”，可在 stock_movements CTE 前增加一行 SELECT 'BEG' ... 模拟期初余额，统一参与 Closing 计算；
- ? 参数化适配：生产环境应将 '2022-03-02' 和 1 替换为预处理变量（如存储过程 IN 参数或应用层绑定），杜绝SQL注入风险。
通过本方案，你将获得一份结构清晰、语义明确、易于维护的库存动向分析SQL，可直接嵌入报表服务或BI工具，为精细化仓储决策提供可靠数据支撑。
