mysql - 错误代码 1054 交叉连接上的未知列

Question

我有三个表 item、store 和 stock_movement。库存变动具有字段 id、item_id、store_id 和 quantity。我想创建一个 View ，显示每家商店每件商品的数量总和。如果商店中的商品没有 stock_movement 条目，则数量应为零。

我提出了以下查询:

SELECT item.*, store.id, SUM(s.quantity) AS quantity
FROM item, store 
LEFT JOIN stock_movement s ON s.item_id = item.id AND store.id = s.store_id
GROUP BY store.id, item.id;

我收到以下错误:

Error Code: 1054 Unknown column 'item.id' in 'on clause'

如果我将查询切换为使用 FROM store, item 未知列会发生变化:

Error Code: 1054 Unknown column 'store.id' in 'on clause'

This answer to use INNER JOIN对我不起作用，因为有些商品和商店没有相应的 stock_movement 条目，即结果缺少几行。

那么有没有办法做这种交叉连接呢？

Answer 1

您需要显式定义您的交叉连接:

select i.id, s.id, coalesce(sum(si.quantity),0) quantity
from store s 
  cross join item i 
  left join storeitem si on s.id = si.storeid and i.id = si.itemid
group by i.id, s.id

• SQL Fiddle Demo

顺便说一句——通过这种方式，你创建了一个笛卡尔积——所有的商店都有所有的元素。