mysql - 将历史表转换为 "ownership"表

Question

对措辞表示歉意，我真的找不到其他方式来表达它。

我有一个像这样的历史表:

id  date         from_id   to_id
1   2010-01-01   A         B
1   2010-01-02   B         C
1   2010-01-03   C         D
2   2010-01-04   A         B
2   2010-01-05   B         X
2   2010-01-06   X         Y

这表示 ID 为“1”的对象在相应日期从 A->B->C->D 传输，“2”则作为 A->B->X->Y 传输。

现在，我想将该表转换为“所有权历史记录”表，如下所示:

id  date_from   date_to     owner_id
1   NULL        2010-01-01  A
1   2010-01-01  2010-01-02  B
1   2010-01-02  2010-01-03  C
1   2010-01-03  2010-01-04  D
1   2010-01-04  NULL        D
2   NULL        2010-01-04  A
2   2010-01-04  2010-01-05  B
2   2010-01-05  2010-01-06  X
2   2010-01-06  NULL        Y

有没有一种有效的方法来做到这一点，使用MySQL？

到目前为止，我已经得到了类似于以下内容的东西，尽管它非常非常慢:

select id, from_id as owner_id, date as date_from, (select date from table where from_id = h.to_id and id = h.id and date > h.date limit 1) as date_to from table h;

Answer 1

您可以使用左连接完成大部分工作:

select h.id, h.date as date_from, h2.date as date_to, h2.to_id
from history h left join
     history h2
     on h.id = h2.id and h.to_id = h2.from_id;

这只是留下了每个 id 的初始记录。为此，请使用union all:

select h.id, h.date as date_from, h2.date as date_to, h2.to_id
from history h left join
     history h2
     on h.id = h2.id and h.to_id = h2.from_id
union all
select h.id, NULL as date_from, h.date as date_to, h.id
from history h;