我有以下两个表,您也可以在SQL fiddle
这里找到:
## Sent Orders ##
CREATE TABLE Send_Orders (
Send_Date DATE,
Product TEXT,
FlowType TEXT
);
INSERT INTO Send_Orders
(Send_Date, Product, FlowType)
VALUES
("2017-05-23", "Product A", "Send"),
("2018-09-10", "Product B", "Send"),
("2018-12-14", "Product B", "Send"),
("2019-01-03", "Product A", "Send"),
("2019-02-15", "Product C", "Send"),
("2017-09-04", "Product C", "Send"),
("2019-01-09", "Product A", "Send"),
("2019-02-16", "Product A", "Send"),
("2019-02-12", "Product A", "Send"),
("2019-02-15", "Product C", "Send"),
("2018-01-03", "Product B", "Send");
## Return Orders ##
CREATE TABLE Return_Orders (
Return_Date DATE,
Product TEXT,
FlowType TEXT
);
INSERT INTO Return_Orders
(Return_Date, Product, FlowType)
VALUES
("2017-06-24", "Product A", "Return"),
("2018-07-11", "Product B", "Return"),
("2018-12-18", "Product B", "Return"),
("2019-02-01", "Product A", "Return"),
("2019-02-22", "Product C", "Return"),
("2017-10-18", "Product C", "Return"),
("2019-04-12", "Product A", "Return"),
("2019-02-19", "Product A", "Return"),
("2019-03-25", "Product A", "Return"),
("2019-04-19", "Product C", "Return"),
("2018-05-17", "Product B", "Return");
现在,我想运行一个查询,并结合列Send_Date
,并Return_Date
在一个名为列Event_Date
这样的结果应该是这样的:
Event_Date Product FlowType
2017-05-23 Product A Send
2017-06-24 Product A Return
2018-09-10 Product B Send
2018-07-11 Product B Return
: : :
: : :
: : :
到目前为止,我可以加入两个表,但是日期显示在两个单独的列中:
SELECT s.Send_Date, r.Return_Date, s.Product, s.FlowType
FROM Send_Orders s
JOIN Return_Orders r ON r.Product = s.Product
GROUP BY 1,2;
我需要在SQL中进行哪些更改以将它们合并为一列?
SELECT Send_Date Event_Date, Product, FlowType FROM Send_Orders
UNION ALL
SELECT Return_Date, Product, FlowType FROM Return_Orders
ORDER BY 1,2
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句