在一个 SQL 查询中合并(合并)两个表中的两列 [英] Combine (Merge) two columns from two tables in one SQL query

问题描述

我有以下两个表，您也可以在 SQL fiddle 此处:

I have the following two tables which you can also find in the SQL fiddle here:

## 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 的列中，因此结果应该如下所示这个:

---

Now, I want to run a query and combine the columns Send_Date and Return_Date in one column called Event_Date so the result should look like this:

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

到目前为止，我可以连接两个表，但日期显示在两个单独的列中:

So far I could join the two tables but the dates are displayed in two seperate columns:

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 中更改什么才能将它们合并为一列?

What do I need to change in the SQL to merge them into one column?

推荐答案

SELECT Send_Date Event_Date, Product, FlowType FROM Send_Orders
UNION ALL
SELECT Return_Date, Product, FlowType FROM Return_Orders
ORDER BY 1,2

这篇关于在一个 SQL 查询中合并(合并)两个表中的两列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！