mysql - 在一个 SQL 查询中合并两个表并使日期值唯一
问题描述
我有以下两个表,您也可以在此处的 SQL 小提琴中找到它们:
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");
我从这里使用 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
: : : :
: : : :
我需要对代码进行哪些更改才能使其正常工作?
解决方案
使用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;
推荐阅读
- android - 我的应用程序在启动时崩溃
- javascript - 根据输入的地址和选择的半径显示谷歌地图
- python - findContour() 检测意外的内部边缘并为它们计算面积奇怪 OpenCV python
- javascript - JavaScript 和 PHP 中浮点数的加法和一致性
- scala - 来自 Akka actor 接收方法的方法调用未处理传递的消息
- excel - Excel:如果开始在前一年,则获取给定年份的开始日期和结束日期之间的月份或结束日期的月份
- google-tag-manager - Google 跟踪代码管理器 (AMP) 上的自定义图片
- spring - spring拦截的恶意请求
- scala - 在 Apache Flink 中读取超过 22 列的 CSV
- python - Pandas Dataframe 检查 id 在时间间隔内是否出现大于 1