MYSQL 合并多行中的两个表

Question

我有两张表，一张用于所有产品，另一个用于新购买的产品。

产品表

Item    Qty
301       2
302       5
303       3
304       4
305       6

购买表

Item     Qty    Status    Date
302       5     used      09-15-2015
303       5     reserve   09-20-2015
301       5     used      09-20-2015
302       5     reserve   09-20-2015
304       5     used      10-15-2015
303       5     reserve   10-15-2015

我想显示产品表中有多少数量，并将采购表的数量加入到产品表

这是我最初的 SQL 查询

SELECT product_name, product_quantity, quantity 
FROM products 
    LEFT OUTER JOIN purchases ON products.ID = purchases.product_ID 
WHERE product_status !='deleted' AND status != 'used'`. 

但是，它只返回购买表中保留的商品。

我想要实现的是

Item    Qty    Reserved Qty    Total
301       2     0                 2
302       5     5                 10
303       3     10                13
304       4     0                 4
305       6     0                 6

更新新的sql查询

SELECT product_name, product_quantity, p.quantity 
FROM products 
    LEFT OUTER JOIN (SELECT * FROM purchases 
        WHERE status = 'reserved' 
        GROUP BY product_ID) AS  p ON p.product_ID = products.ID 
WHERE products.product_status !='deleted'`

但它在保留数量上返回 0。

Answer 1

就查询而言，您几乎已经完成了，只是您需要对保留项目的数量进行求和，然后使用GROUP BY，有点像这样

SELECT
    pd.item AS 'Item', pd.qty AS 'Qty', IFNULL(pr.reserved_quantity, 0) AS 'Reserved Qty', (pd.qty+IFNULL(pr.reserved_quantity, 0)) AS 'Total'
FROM
    product pd
    LEFT OUTER JOIN (SELECT item, SUM(qty) AS 'reserved_quantity' FROM purchases WHERE `status`='reserve' GROUP BY 1) AS pr ON pd.item=pr.item