技术需求：编写SQL查询对比两个库存表并生成差异结果

要得到包含所有物料的实际库存、盘点库存及差异的报表，需要先分别聚合两张表的库存数据，再关联全量物料列表计算结果，具体SQL如下：

WITH counted AS (
    SELECT part_number AS material, SUM(quantity) AS counted_stock
    FROM 11_counted_stock
    GROUP BY part_number
),
actual AS (
    SELECT material, SUM(`A.stock`) AS stock
    FROM 11_stock_actual_whs_costed
    GROUP BY material
),
all_materials AS (
    SELECT part_number AS material FROM 11_counted_stock
    UNION
    SELECT material FROM 11_stock_actual_whs_costed
)
SELECT 
    am.material AS Material,
    COALESCE(a.stock, 0) AS Stock,
    COALESCE(c.counted_stock, 0) AS Counted_Stock,
    COALESCE(a.stock, 0) - COALESCE(c.counted_stock, 0) AS Difference
FROM all_materials am
LEFT JOIN actual a ON am.material = a.material
LEFT JOIN counted c ON am.material = c.material
ORDER BY am.material;

代码说明 #

• counted 子查询：对盘点表按物料分组，计算该物料的总盘点数量（若同物料有多条记录，SUM能确保统计准确）
• actual 子查询：对实际库存表按物料分组，计算总实际库存（注意字段A.stock包含特殊字符，需用反引号包裹）
• all_materials 子查询：通过UNION获取两张表中所有唯一物料，避免遗漏仅在单表存在的物料
• 最终查询：用左连接关联全量物料与两个聚合结果，用COALESCE将NULL值替换为0（代表该物料在对应表无记录），最后计算实际库存与盘点库存的差值

原SQL问题说明 #

你之前的SQL通过UNION合并两张表的数据后再求和，会将实际库存与盘点库存的数值混在一起计算，无法区分两个独立的数值，因此无法得到目标报表。

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