SQL Group By 和内部联接

Question

SELECT
   C.SOId, Ser.TotalService, S.TotalSales 
FROM
  salesorder C
  INNER JOIN  (SELECT SOId,SUM(charge) as TotalService FROM sales_serviceline GROUP BY SOId)  Ser
  ON C.SOId = Ser.SOId
  INNER JOIN  (SELECT SOId,SUM(PriceEach*qty) as TotalSales FROM  salesline  GROUP BY  SOId)  S
  ON C.SOId = S.SOId

解释 :
一个 SalesOrder 有多个销售线
一个 SalesOrder 有许多销售线服务的

因此，如果销售订单具有 saleslins 和 salesline_service 那么上面的查询成功地检索了该销售订单的总金额但如果只有 saltline 或只有销售订单没有重试的销售线服务如何更改此查询以获取所有结果？

Answer 1

我认为您正在寻找 LEFT JOIN .如果 Ser 中没有行或 S然后是 LEFT JOIN将使TotalService或 TotalSales NULL

SELECT
   C.SOId, Ser.TotalService, S.TotalSales 
FROM
  salesorder C
  LEFT JOIN  (SELECT SOId,SUM(charge) as TotalService FROM sales_serviceline GROUP BY SOId)  Ser
  ON C.SOId = Ser.SOId
  LEFT JOIN  (SELECT SOId,SUM(PriceEach*qty) as TotalSales FROM  salesline  GROUP BY  SOId)  S
  ON C.SOId = S.SOId

如果您不想将它们设为 NULL。然后就可以退出easy use COALESCE() .像这样:

SELECT
   C.SOId, 
   COALESCE(Ser.TotalService,0) AS TotalService, 
   COALESCE(S.TotalSales,0) AS TotalSales
FROM
  salesorder C
  LEFT JOIN  (SELECT SOId,SUM(charge) as TotalService FROM sales_serviceline GROUP BY SOId)  Ser
  ON C.SOId = Ser.SOId
  LEFT JOIN  (SELECT SOId,SUM(PriceEach*qty) as TotalSales FROM  salesline  GROUP BY  SOId)  S
  ON C.SOId = S.SOId