我有以下两个表,您也可以在 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");
我使用VBAhere合并两个表:
(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
: : : :
: : : :
我需要对代码进行哪些更改才能使其正常工作?