代码之家  ›  专栏  ›  技术社区  ›  Michi

在一个SQL查询中合并(合并)两个表中的两列

  •  3
  • Michi  · 技术社区  · 5 年前

    我有以下两张表,你也可以在 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

    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中进行哪些更改?

    2 回复  |  直到 5 年前
        1
  •  0
  •   Akina    5 年前
    SELECT Send_Date Event_Date, Product, FlowType FROM Send_Orders
    UNION ALL
    SELECT Return_Date, Product, FlowType FROM Return_Orders
    ORDER BY 1,2
    
        2
  •  0
  •   Vijay Pawar    5 年前

    从发送订单中选择发送日期作为事件日期、产品、流程类型 全部联合 从退货订单中选择退货日期作为事件日期、产品、流程类型 按2订购