SQL多表关联查询：实现多仓库库存余额同表展示

嘿，我太懂你现在的困扰啦——刚入门用PHP+SQL做库存系统，想把两个仓库的库存余额汇总到一张表里，结果之前的查询要么数字不对，要么还漏了只在单个仓库存在的物品，确实挺头疼的😅

先给你分析下之前查询的问题出在哪：

• 你用了FROM table1, table2这种写法，本质是笛卡尔积关联，会把两个表的行两两匹配。比如table1里item1有2条记录，table2里item1有2条记录，关联后会变成4条，sum的时候就会把table1的出入库数据重复计算2次，table2的重复计算2次，结果自然不对；
• WHERE table1.item_id = table2.item_id属于内连接，只会保留两个表中都存在的item_id，所以如果某个物品只在其中一个仓库有记录，就会被漏掉。

接下来给你两种可行的解决方案，帮你实现想要的效果：

方法一：用CTE拆分逻辑（清晰易维护） #

这种方式把每个步骤拆分出来，可读性很强，适合大多数支持CTE的SQL数据库（比如MySQL 8.0+、PostgreSQL、SQL Server等）：

-- 第一步：获取所有存在的物品ID（不管在哪个仓库）
WITH all_items AS (
    SELECT item_id FROM table1
    UNION
    SELECT item_id FROM table2
),
-- 第二步：计算仓库1的库存余额
warehouse1_balances AS (
    SELECT item_id, SUM(`in` - `out`) AS warehouse1
    FROM table1
    GROUP BY item_id
),
-- 第三步：计算仓库2的库存余额
warehouse2_balances AS (
    SELECT item_id, SUM(`in` - `out`) AS warehouse2
    FROM table2
    GROUP BY item_id
)
-- 最后关联所有物品和两个仓库的余额，没有数据的仓库用0填充
SELECT 
    ai.item_id,
    COALESCE(w1.warehouse1, 0) AS warehouse1,
    COALESCE(w2.warehouse2, 0) AS warehouse2
FROM all_items ai
LEFT JOIN warehouse1_balances w1 ON ai.item_id = w1.item_id
LEFT JOIN warehouse2_balances w2 ON ai.item_id = w2.item_id
ORDER BY ai.item_id;

关键说明： #

• UNION用来获取所有不重复的item_id，确保不会漏掉任何一个物品；
• LEFT JOIN保证即使某个物品只在一个仓库有数据，也会被显示出来；
• COALESCE函数把NULL替换成0，这样没有库存记录的仓库余额会显示0，更符合库存统计的逻辑；
• 注意in是SQL的关键字，所以要用反引号`包裹，避免语法错误。

方法二：子查询写法（兼容老版本数据库） #

如果你的数据库不支持CTE（比如MySQL 5.7及以下），可以用子查询的方式实现同样的效果：

SELECT 
    ai.item_id,
    COALESCE(w1.warehouse1, 0) AS warehouse1,
    COALESCE(w2.warehouse2, 0) AS warehouse2
FROM (
    SELECT item_id FROM table1
    UNION
    SELECT item_id FROM table2
) ai
LEFT JOIN (
    SELECT item_id, SUM(`in` - `out`) AS warehouse1
    FROM table1
    GROUP BY item_id
) w1 ON ai.item_id = w1.item_id
LEFT JOIN (
    SELECT item_id, SUM(`in` - `out`) AS warehouse2
    FROM table2
    GROUP BY item_id
) w2 ON ai.item_id = w2.item_id
ORDER BY ai.item_id;

额外的小建议：优化数据库结构 #

其实从长期维护的角度来看，把两个仓库的表合并成一张会更灵活，比如新增一个warehouse_id字段区分仓库：

CREATE TABLE inventory_transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    warehouse_id INT COMMENT '1=仓库1，2=仓库2',
    item_id VARCHAR(50),
    `in` INT DEFAULT 0,
    `out` INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

这样后续查询多仓库余额会更简单，甚至新增仓库也不用改表结构：

SELECT 
    item_id,
    SUM(CASE WHEN warehouse_id = 1 THEN `in` - `out` ELSE 0 END) AS warehouse1,
    SUM(CASE WHEN warehouse_id = 2 THEN `in` - `out` ELSE 0 END) AS warehouse2
FROM inventory_transactions
GROUP BY item_id;

备注：内容来源于stack exchange，提问作者user23913928