mysql - 从多个表中选择，即使一个表没有任何对应记录

Question

我有三个表:产品、销售和购买。

我需要获取产品的销售总额和购买总额。即使没有进行任何销售，我也需要它来显示购买情况，反之亦然。像这样的东西:

Product   | Sum of sales | Sum of purchases |
____________________________________________
product 1 | 10000        | 45000            |
product 2 | 20000        | 0 (or null)      |
product 3 | 0(or null)   | 20000            |

我已经尝试过此操作，但它只会返回同时进行销售和购买的产品:

select prod.name, sum(s.total), sum(p.total)
from product prod
inner join sale s on s.prod_id = prod.id
inner join purchase p on p.prod_id = prod.id
group by 1;

提前致谢。

Answer 1

您的查询有几个问题。首先，由于 INNER JOIN 它只会(如您所见)返回同时具有销售和购买的产品的值。这可以通过使用LEFT JOIN(使用可选的COALESCE将null值转换为0)来解决。第二个问题是，如果产品有多次销售或购买，您将从 JOIN 中获得重复的行。要解决这个问题，您需要在销售和购买的派生表中执行聚合:

SELECT pr.name,
       s.total AS sales,                    -- will show null if no sales
       COALESCE(p.total, 0) AS purchases    -- will show 0 if no purchases
FROM product pr
LEFT JOIN (SELECT prod_id, SUM(total) AS total
           FROM sale
           GROUP BY prod_id) s ON s.prod_id = pr.id
LEFT JOIN (SELECT prod_id, SUM(total) AS total
           FROM purchase
           GROUP BY prod_id) p ON p.prod_id = pr.id