一个left join 的问题（结果翻倍）
我现在有三张表：
Product 表 : id，qty
1 100
2 200
Inventory表（库存） ：product_id , qty ,warehouse_id
1 20 1
1 30 2
2 50 1
Sell 表（销售）： product_id, qty
1 20
1 10
现在要查询出 产品id，总数量，库存量，未入库量，销售量
要用一个产品就一条数据表示（group by ）。用如下的sql ，查询出来，产品id 为 1的库存量会算成100 ，本来应该是50的，会翻倍算，要如何写sql ？
SELECT product.id,
product.name,
product.qty AS totalqty,
if(sum(inventory.qty) IS NULL, 0, sum(inventory.qty)) AS inventory_qty,
if(sum(sell.qty) IS NULL, 0, sum(sell.qty)) AS sellqty,
product.qty – IF(sum(inventory.qty) IS NULL, 0, sum(inventory.qty)) –
IF(sum(sell.qty) IS NULL, 0, sum(sell.qty)) AS out_inventory_qty
FROM product
LEFT JOIN inventory
ON product.id = inventory.product_id
LEFT JOIN sell
ON product.id = sell.product_id
GROUP BY product.id, product.name
先对Inventory表SUM，然后再JOIN
select
from aaa left join (select id,sum() from Inventory group by ..) x
-
SQL code
select A.id,A.qty as 总数量,B.qty as 库存量,A.qty-B.qty as 未入库量,C.qty as 销售量 from product A left join (select product_id,sum(qty) as qty from invertory group by product_id)B on A.id = B.prduct_id left join sell C on A.id = C.id
你下把前面个left 看做一个结果集然后在和sell做连接！
