本文详解如何使用 mysql cte 与 union all 构建动态库存流水视图，精准汇总指定日期、指定仓库下各商品的入库（purchase）、出库（order）数量及实时结余，支持多维度分组与排序。
在库存管理系统中，常需按具体日期和指定仓库快速查看每种商品的出入库明细及当日结余（即“期初 + 入库 − 出库”）。由于出入库数据分散在多个关联表（purchase/purchase_item 与 order/order_item），直接 JOIN 易导致笛卡尔积或聚合失真。推荐采用 CTE（Common Table Expression）+ UNION ALL 的方式，将“入库”与“出库”作为两类独立动作统一建模，再合并计算结余。
以下为完整、可执行的 MySQL 8.0+ 查询方案：
WITH stock_movements AS (
-- 【入库记录】：标记为 'P'，仅取 purchase_item 中的 qty，outward 置 0
SELECT
'P' AS movement_type,
p.id AS product_id,
CONCAT(p.product_name, '(P)') AS product_label,
COALESCE(CAST(pi.qty AS DECIMAL(12,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 AND pu.warehouse_id IS NOT NULL
UNION ALL
-- 【出库记录】：标记为 'O'，仅取 order_item 中的 qty，inward 置 0
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(12,2)), 0) AS outward_qty,
ord.warehouse_id,
ord.`date` AS transaction_date
FROM products p
LEFT JOIN order_item oi ON p.id = oi.product_id
LEFT JOIN `order` ord ON oi.order_id = ord.id
WHERE ord.`date` IS NOT NULL AND ord.warehouse_id IS NOT NULL
)
-- 主查询：按产品+动作类型聚合，并计算结余（累计 inward − outward）
SELECT
product_label AS `Product name`,
SUM(inward_qty) AS `Inward`,
SUM(outward_qty) AS `Outward`,
SUM(inward_qty) - SUM(outward_qty) AS `Closing`
FROM stock_movements
WHERE transaction_date = '2022-03-02' -- ✅ 按需替换为查询日期
AND warehouse_id = 1 -- ✅ 按需替换为仓库ID
GROUP BY product_id, product_label, movement_type
ORDER BY product_id, movement_type DESC;✅ 关键设计说明：
- 使用 CONCAT(p.product_name, '(P)') 和 '(O)' 清晰区分出入库行，避免混淆；
- COALESCE(CAST(... AS DECIMAL), 0) 统一处理 qty 字段可能为字符串（如示例中 VARCHAR 类型）的问题，确保数值计算安全；
- LEFT JOIN 保证即使某商品无出入库记录，仍会出现在结果中（若需仅显示有变动的商品，可改为 INNER JOIN）；
- GROUP BY product_id, product_label, movement_type 精确控制分组粒度，防止同名不同动作被错误合并；
- SUM(inward_qty) - SUM(outward_qty) 直接得出结余，逻辑透明、易于验证。
⚠️ 注意事项：
- 表名 order 是 MySQL 保留字，务必用反引号包裹（`order`），否则报错；
- 若 qty 字段实际为字符串（如示例 DDL 所示），必须显式 CAST 转换为数值类型，否则 SUM() 将静默失败或返回 0；
- 本方案默认以 当日发生的出入库 为依据计算结余；如需包含期初库存（products.qty），应在 CTE 中增加一行“期初”记录（movement_type = 'B'），并将其 inward_qty 设为 p.qty，outward_qty = 0；
- 性能优化建议：为 purchase.date, purchase.warehouse_id, purchase_item.product_id, order.date, order.warehouse_id, order_item.product_id 建立联合索引。
该查询结构清晰、扩展性强，可无缝集成至报表系统或 BI 工具，是构建实时库存看板的核心 SQL 模板。
