mysql - 如何将多个事务表合并为一个查询，每个表代表一个状态？

Question

我有与主表订单相关的事务表。我想将所有这些交易合并到一个订单历史查询中，以便每笔交易及其日期都作为订单在某个时间点的状态呈现。

什么查询会提供以下输出？

订单表

Order ID
1
2

订单确认表

Order Confirmation ID    Date
1                        2015-08-01
2                        2015-08-01

订单取消表

Order Cancellation ID    Date
1                        2015-08-02

订单完成表

Order Completion ID    Date
2                      2015-08-02

输出:

Order ID    Date          Status
1           2015-08-01    Confirmed
2           2015-08-01    Confirmed
1           2015-08-02    Cancelled
2           2015-08-02    Completed

Answer 1

select o.orderid,
oc.date,
'Cancelled' as status   
from order o
join order_cancellation oc 
on o.orderid = oc.orderid
union
select o.orderid,
ol.date,
'Completed' as status 
from order o
join order_completed ol
on o.orderid = ol.orderid

您可以使用联合查询来实现此目的。