mysql - 在一个 SQL 查询中合并两个表并使日期值唯一

Question

我有以下两个表，您也可以在 SQL fiddle here 中找到它们:

CREATE TABLE Inbound (
    Inbound_Date DATE,
    Product TEXT,
    InboundType TEXT,
    Quantity VARCHAR(255)
);

INSERT INTO Inbound
(Inbound_Date, Product, InboundType, Quantity)
VALUES 
("2017-05-23", "Product A", "Supplier", "400"),
("2018-09-10", "Product B", "Supplier", "200"),
("2018-12-14", "Product B", "Supplier", "600"),
("2019-01-03", "Product A", "Return", "700"),
("2019-02-15", "Product C", "Supplier", "650"),
("2017-09-04", "Product C", "Supplier", "380"),
("2019-01-09", "Product A", "Return", "120"),
("2019-02-16", "Product A", "Return", "470"),
("2019-02-12", "Product A", "Supplier", "920"),
("2019-02-15", "Product C", "Return", "860"),
("2018-01-03", "Product B", "Supplier", "610");


CREATE TABLE Outbound (
    Outbound_Date DATE,
    Product TEXT,
    OutboundType TEXT
);

INSERT INTO Outbound
(Outbound_Date, Product, OutboundType)
VALUES 
("2017-05-23", "Product A", "Sale_US"),
("2018-09-10", "Product B", "Sale_DE"),
("2018-12-18", "Product B", "Sale_DE"),
("2019-02-01", "Product A", "Sale_DE"),
("2019-02-22", "Product C", "Sale_FR"),
("2017-10-18", "Product C", "Sale_NL"),
("2019-04-12", "Product A", "Sale_US"),
("2019-04-12", "Product A", "Sale_FR"),
("2019-04-12", "Product A", "Sale_FR"),
("2019-04-19", "Product C", "Sale_US"),
("2018-05-17", "Product B", "Sale_DE");

我使用来自 here 的 VBA合并两个表:

(SELECT 
   Inbound_Date As Date, 
   Product, 
   SUM(Quantity) as Inbound, 0 as Outbound
 FROM Inbound
 GROUP BY 1,2
) 

UNION ALL

(SELECT
   Outbound_Date,
   Product,
   0 as Inbound, COUNT("Outbound_Type")  as Outbound 
 FROM Outbound
 GROUP BY 1,2
)

ORDER BY 1,2;

所有这一切都完美无缺。

但是，现在我希望日期显示为唯一的。
结果应如下所示:

Date           Product       Inbound        Outbound
2017-05-13     Product A     400            1
2017-09-04     Product C     380            0
2017-10-18     Product C      0             1
:              :             :              :
:              :             :              :
2018-09-10     Product B     200            1
:              :             :              :
:              :             :              :

我需要在代码中更改什么才能使其正常工作？

Answer 1

使用 union all和 group by :

SELECT Date, Product, SUM(Inbound) as Inbound, SUM(Outbound) as Outbound
FROM ((SELECT Inbound_Date As Date, Product, SUM(Quantity) as Inbound, 0 as Outbound
      FROM Inbound
      GROUP BY 1,2
     ) UNION ALL
     (SELECT Outbound_Date, Product, 0 as Inbound, COUNT(*)  as Outbound 
      FROM Outbound
      GROUP BY 1,2
     )
    ) io
GROUP BY Date, Product;