将一个表与另外两个表连接起来 - join one table with two other tables

join one table with two other tables

本文关键字：两个起来连接一个 | 更新日期: 2023-09-27

我有3张表

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;

使用IFNULL代替。因为12 + null = null

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

并且由于您在resultset中添加了pr.name，因此您还需要将其放在GROUP BY子句中。