SQL 查询用 JOINS 或其他东西替换 UNION ALL

Question

现在我有3张 table

订单:

ID | Date | Machine | Planning

好作品:

ID | Date | Machine | Pieces | Product

坏点:

ID | Date | Machine | Pieces | Product | Component

查询的输出是

Date | Machine | Planning | GoodPieces | BadPieces

实际的查询是这样的:

SELECT
  data.Date AS Date,
  data.Machine AS Machine,
  SUM(CASE WHEN data.type = 'Planning' THEN data.value END ) AS Planning,
  SUM(CASE WHEN data.type = 'GoodPieces' THEN data.value END ) AS GoodPieces,
  SUM(CASE WHEN data.type = 'BadPieces' THEN data.value END ) AS BadPieces
FROM
      ( SELECT
          'Planning' AS Type,
          Date AS Date,
          Machine AS Machine,
          Planning AS Value
        FROM Orders
        UNION ALL
        SELECT
          'GoodPieces',
          Date,
          Machine,
          Pieces AS Value
        FROM GoodPieces
        UNION ALL
        SELECT
          'BadPieces'
          Date,
          Machine,
          Pieces AS Value
        FROM BadPieces ) AS data
GROUP BY
   Date,
   Machine

我的问题是是否有办法用这 3 个表获得相同的输出

订单:

ID | Date | Machine| Planning

好作品:

OrderID | Pieces | Product

坏点:

OrderID | Pieces | Product | Component

谢谢，

Answer 1

当然，只需在表中LEFT JOIN 并对适当的列求和:

SELECT
  O.Date,
  O.Machine,
  SUM(COALESCE(O.Planning, 0)) AS Planning,
  SUM(COALESCE(G.Pieces, 0)) AS GoodPieces,
  SUM(COALESCE(B.Pieces, 0)) AS BadPieces
FROM
  Orders O
LEFT JOIN
  (SELECT G.OrderID,
     SUM(COALESCE(G.Pieces, 0)) AS GoodPieces
   FROM GoodPieces G
   GROUP BY G.OrderID) G ON G.OrderID = O.ID
LEFT JOIN
  (SELECT B.OrderID,
     SUM(COALESCE(B.Pieces, 0)) AS BadPieces
   FROM BadPieces B
   GROUP BY B.OrderID) B ON B.OrderID = O.ID
GROUP BY
  O.Date,
  O.Machine;

演示:http://www.sqlfiddle.com/#!3/09a73/17

编辑:已更新以处理 @MikaelEriksson 在评论中给出的观点。