php - mysql-将一个表与另外两个表联接

我有三张桌子

Products
[id, name]

Purchases
[id, product_id, quantity, date]

Sales
[id, product_id, quantity, date]

我想列出每种产品的剩余数量。
我试过下面的查询，但它不起作用。

SELECT pr.id, pr.name, (sum(pu.quantity))-sum(s.quantity) as quantity
FROM `products` pr
join purchase pu ON pu.product_id = pr.id
left join sales s ON s.product_id = pr.id
GROUP BY pr.id

我知道上面的查询为purchase和sales创建了重复的行，因为我们将表和产品连接起来。
请帮助我编写正确的查询以查找剩余的产品数量

最佳答案

您需要在join之前进行聚合。from子句中有一个方法是显式的：

SELECT pr.id, pr.name,
       (coalesce(pu.sumq, 0) - coalesce(s.sumq, 0)) as quantity
FROM `products` pr left join
     (SELECT pu.product_id, SUM(pu.quantity) as sumq
      FROM purchase pu
      GROUP BY pu.product_id
     ) pu
     ON pu.product_id = pr.id left join
     (SELECT s.product_id, SUM(s.quantity) as sumq
      FROM sales s
      GROUP BY s.product_id
     ) s
     ON s.product_id = pr.id
GROUP BY pr.id, pr.name;

关于php - mysql-将一个表与另外两个表联接，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/33035786/