mysql - "INNER JOIN"和 "LEFT JOIN"与 "GROUP BY"

Question

我的表格是这样的

sales
----------------------------------------------------------
id  ordernumber             quantity  category_id    price
1   402-9182243-8008368     1         3              22.95
2   406-3666671-8627555     2         3               6.95
3   303-1935495-5532309     1         1               7.95
4   171-5799800-1198702     1         2             159.95
5   403-2398078-4901169     2         2              18.95

category
--------------
id  name
1   bikes
2   shoes
3   planes

returns
--------------
id  ordernumber          quantity   costs
1   402-9182243-8008368  1          22.95   
2   402-9182243-8008368              5.95   // return shipping fee

这是我的问题

    SELECT c.name,
           SUM(v.quantity) AS sold,               # wrong
           SUM(s.quantity * s.price) AS turnover, # wrong
           SUM(r.costs) AS returncosts,
      FROM sales AS s
INNER JOIN categories AS c ON c.id = s.category_id  
 LEFT JOIN returns AS r ON r.ordernumber = s.ordernumber 
  GROUP BY c.name

我有一些带有聚合函数的内部连接。但我还需要“返回”和“左连接”(我认为)。使用 Left Join，我的聚合函数不再起作用。Left Join 添加额外的行。用于 sum() 的附加数据。

我需要一个查询，所以每一列稍后都可以排序。我很乐意提供任何帮助。最好的问候

Answer 1

它是半笛卡尔积，因为订单号在返回表中不是唯一的。

如果我们删除聚合函数并返回详细信息行，我们可以看到发生了什么。

一种可能的方法是在内联 View 中预先聚合返回，以便返回订单号的唯一值。

假设 ordernumber 在 sales 表中是唯一的，那么是这样的:

SELECT c.name
     , SUM(s.quantity)                  AS sold
     , SUM(s.quantity * s.price)        AS turnover
     , SUM(r.returncosts)               AS returncosts
  FROM sales s
  JOIN categories c
    ON c.id = s.category_id
  LEFT 
  JOIN ( SELECT t.ordernumber 
              , SUM(t.costs) AS returncosts
           FROM returns t
          GROUP
             BY t.ordernumber
       ) r 
    ON r.ordernumber = s.ordernumber
 GROUP
    BY c.name