如何在MySQL中合并两个查询以生成库存报表？

看起来你正在整合两个MySQL查询来生成完整的库存报表，我先帮你把现有的第一个查询优化得更简洁易读，再结合常见的库存报表场景，给你讲讲如何合并第二个查询（虽然你没贴完整第二个查询的内容，但我会覆盖最常用的两种合并场景）。

优化现有查询 #

你当前的查询用了多个CASE WHEN来计算不同类型的库存变动，其实可以用MySQL的IF()函数简化写法，逻辑完全一致但代码更紧凑：

SELECT 
    product_id, 
    product_name, 
    SUM(IF(type = 'I', quantity, 0)) AS import,
    SUM(IF(type = 'O', quantity, 0)) AS export,
    SUM(IF(type = 'R', quantity, 0)) AS refund,
    -- 直接复用计算逻辑，避免重复写SUM
    (SUM(IF(type = 'I', quantity, 0)) + SUM(IF(type = 'R', quantity, 0)) - SUM(IF(type = 'O', quantity, 0))) AS available
FROM `i1ntl_ldn_soft_inventory_detail`
WHERE product_id = 2772 
  -- 补充你的日期条件，比如 date >= '2024-01-01'
GROUP BY product_id, product_name;

注意：如果你的MySQL版本支持CTE（8.0+），还可以把基础聚合结果放到CTE里，再计算available，进一步提升可读性：

WITH inventory_agg AS (
    SELECT 
        product_id, 
        product_name, 
        SUM(IF(type = 'I', quantity, 0)) AS import,
        SUM(IF(type = 'O', quantity, 0)) AS export,
        SUM(IF(type = 'R', quantity, 0)) AS refund
    FROM `i1ntl_ldn_soft_inventory_detail`
    WHERE product_id = 2772 
      -- 补充日期条件
    GROUP BY product_id, product_name
)
SELECT 
    *,
    (import + refund - export) AS available
FROM inventory_agg;

合并查询的两种常见场景 #

场景1：两个查询是同维度的不同数据集（比如不同产品/时间范围） #

如果第二个查询是针对其他产品、不同时间区间的同结构库存数据，用UNION ALL合并结果（UNION会去重，库存报表一般用UNION ALL保留所有原始数据）：

-- 第一个查询：product_id=2772的库存
SELECT 
    product_id, 
    product_name, 
    SUM(IF(type = 'I', quantity, 0)) AS import,
    SUM(IF(type = 'O', quantity, 0)) AS export,
    SUM(IF(type = 'R', quantity, 0)) AS refund,
    (SUM(IF(type = 'I', quantity, 0)) + SUM(IF(type = 'R', quantity, 0)) - SUM(IF(type = 'O', quantity, 0))) AS available
FROM `i1ntl_ldn_soft_inventory_detail`
WHERE product_id = 2772 
  AND date BETWEEN '2024-01-01' AND '2024-06-30'
GROUP BY product_id, product_name

UNION ALL

-- 第二个查询：比如product_id=2773的库存，或者不同时间范围
SELECT 
    product_id, 
    product_name, 
    SUM(IF(type = 'I', quantity, 0)) AS import,
    SUM(IF(type = 'O', quantity, 0)) AS export,
    SUM(IF(type = 'R', quantity, 0)) AS refund,
    (SUM(IF(type = 'I', quantity, 0)) + SUM(IF(type = 'R', quantity, 0)) - SUM(IF(type = 'O', quantity, 0))) AS available
FROM `i1ntl_ldn_soft_inventory_detail`
WHERE product_id = 2773 
  AND date BETWEEN '2024-01-01' AND '2024-06-30'
GROUP BY product_id, product_name;

场景2：两个查询是不同维度的关联数据（比如库存明细+产品基础信息） #

如果第二个查询是产品的基础信息（比如分类、供应商、成本价），用JOIN关联两个查询的结果，生成更完整的报表：

WITH inventory_agg AS (
    SELECT 
        product_id, 
        product_name, 
        SUM(IF(type = 'I', quantity, 0)) AS import,
        SUM(IF(type = 'O', quantity, 0)) AS export,
        SUM(IF(type = 'R', quantity, 0)) AS refund,
        (SUM(IF(type = 'I', quantity, 0)) + SUM(IF(type = 'R', quantity, 0)) - SUM(IF(type = 'O', quantity, 0))) AS available
    FROM `i1ntl_ldn_soft_inventory_detail`
    WHERE product_id = 2772 
      -- 补充日期条件
    GROUP BY product_id, product_name
)
SELECT 
    ia.*,
    p.category,
    p.supplier_name,
    p.cost_price
FROM inventory_agg ia
-- 关联产品基础表，假设表名为products
JOIN products p ON ia.product_id = p.product_id;

额外提示 #

• 如果需要生成总计行，可以在GROUP BY后加上WITH ROLLUP，自动计算所有产品的汇总数据。
• 确保两个查询合并时，列的数量、顺序、数据类型完全一致（UNION ALL要求）。
• 如果第二个查询是其他逻辑（比如库存预警、历史库存），可以把具体内容补充后，我再帮你调整合并方案。

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