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

2021-11-20 00:00:00 sql mysql

我有以下两个表，你也可以在 SQL fiddle 这里找到:

I have the following two tables which you can also find in the 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");

我使用 此处 合并两个表:

I use the VBA from here to merge the two tables:

(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;

所有这些都完美无缺.

但是，现在我希望日期显示为唯一的.
结果应该是这样的:

However, now I want that the dates are displayed unique.
The result should look like this:

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
:              :             :              :
:              :             :              :

我需要对代码进行哪些更改才能使其正常工作?

What do I need to change in my code to make it work?

推荐答案

使用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;

相关文章