SQL子查询如何实现库存余额计算-SQL-PHP中文网

子查询必须作为select列表达式或where条件存在，不可独立执行；正确做法是用相关/非相关子查询分别计算期初、入库、出库，或用left join预聚合避免笛卡尔积与null传播。

sql子查询如何实现库存余额计算

子查询必须用在 SELECT 列表或 WHERE 条件中，不能独立执行

直接写 SELECT qty FROM stock_log WHERE item_id = 1 然后拿结果去算余额？不行——SQL 不允许孤立的子查询。库存余额本质是「期初 + 入库 - 出库」，必须把三部分整合进一个查询上下文里。

常见错误是试图先查期初，再查入库总和，最后手动相减，结果报错 subquery returns more than 1 row 或语法错误。正确做法是让子查询作为列表达式或过滤条件存在：

用相关子查询计算单个商品的累计入库量：(SELECT COALESCE(SUM(qty), 0) FROM stock_in WHERE item_id = t.item_id)
用非相关子查询获取全局期初值（如果所有商品共用同一期初）：(SELECT init_qty FROM stock_init LIMIT 1)
避免在 WHERE 中对聚合子查询引用外部表字段，否则可能触发 Unknown column 't.item_id' in field list

用 LEFT JOIN 替代多层嵌套子查询更安全

当要同时算多个商品的余额时，三层子查询（期初、入库、出库各一个）会让语句臃肿且难调试。MySQL 8.0+ 或 PostgreSQL 下容易因相关性判断失误导致性能骤降，甚至返回重复行。

推荐用 LEFT JOIN 预聚合再关联：

SELECT 
  t.item_id,
  COALESCE(init.qty, 0) AS init_qty,
  COALESCE(in_sum.total_in, 0) AS total_in,
  COALESCE(out_sum.total_out, 0) AS total_out,
  COALESCE(init.qty, 0) + COALESCE(in_sum.total_in, 0) - COALESCE(out_sum.total_out, 0) AS balance
FROM items t
LEFT JOIN (SELECT item_id, SUM(qty) AS qty FROM stock_init GROUP BY item_id) init ON init.item_id = t.item_id
LEFT JOIN (SELECT item_id, SUM(qty) AS total_in FROM stock_in GROUP BY item_id) in_sum ON in_sum.item_id = t.item_id
LEFT JOIN (SELECT item_id, SUM(qty) AS total_out FROM stock_out GROUP BY item_id) out_sum ON out_sum.item_id = t.item_id

注意点：

FinGenius

FinGenius是一款AI智能体工具，全球首个A股AI金融博弈智能体应用。

下载

每个子查询必须带 GROUP BY item_id，否则 JOIN 会爆炸式笛卡尔积
COALESCE 必须包裹每项，否则任一 NULL 都会让整行 balance 变成 NULL
如果 stock_init 是单行配置表（无 item_id 字段），就改用非相关子查询：(SELECT qty FROM stock_init)

WHERE 中子查询不能引用主查询的聚合字段

想筛出余额小于 0 的商品？别写 WHERE (init + in - out) —— 这个表达式在子查询里无法访问主查询的别名字段。常见错误写法：

SELECT item_id, (SELECT ... ) AS balance FROM items WHERE balance <p>正确方式只有两种：</p>

用 HAVING（仅适用于含 GROUP BY 的场景）
把整个计算逻辑包进 WHERE 子查询里：WHERE (COALESCE((SELECT qty FROM stock_init WHERE item_id = items.item_id), 0) + ... )
或者用派生表（即子查询当临时表）：SELECT * FROM (SELECT ..., balance FROM ...) t WHERE t.balance

最后一种最清晰，但要注意 MySQL 5.7 默认不支持对派生表直接加索引，大数据量时可能慢。

时间范围控制必须下推到子查询内部

查「近30天」的余额？别在最外层加 WHERE log_time > DATE_SUB(NOW(), INTERVAL 30 DAY)——那会先算全量再过滤，极慢。必须把时间条件塞进每个子查询的 WHERE 里：

入库子查询：SELECT item_id, SUM(qty) FROM stock_in WHERE log_time >= '2024-05-01' GROUP BY item_id
出库子查询同理，且注意 log_time 字段是否为 DATETIME 类型，避免隐式转换导致索引失效
期初值一般不随时间变化，但如果业务支持按日期快照（如每日盘存），就得把快照日期作为参数传入子查询

真正容易被忽略的是事务一致性：如果入库和出库日志表没用同一事务提交，或者没加 FOR UPDATE 锁，实时查出的余额可能瞬间不准确——这不是 SQL 写法问题，而是业务模型约束。