我有以下两个表,您也可以在SQL fiddle
here http://www.sqlfiddle.com/#!9/70dba18/9:
## 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
and 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 中更改什么才能将它们合并到一列中?