mysql - MySQL填充所有valid_to和modified_by列，知道所有valid_from和created_by值

这是我正在处理的表的示例：

+----+-----------+-------+------------+---------------------+-------------+----------+
| id | object_id | value | created_by |     valid_from      | modified_by | valid_to |
+----+-----------+-------+------------+---------------------+-------------+----------+
|  1 |       333 | a     | Paul       | 2016-06-10 08:15:57 |             |          |
|  3 |       333 | b     | Naomi      | 2016-06-10 09:39:37 |             |          |
|  8 |       333 | c     | Anne       | 2016-06-10 09:46:09 |             |          |
+----+-----------+-------+------------+---------------------+-------------+----------+

什么是（最好是）单个更新语句，该语句会将上面的表更新为下面的表？

+----+-----------+-------+------------+---------------------+-------------+---------------------+
| id | object_id | value | created_by |     valid_from      | modified_by |      valid_to       |
+----+-----------+-------+------------+---------------------+-------------+---------------------+
|  1 |       333 | a     | Paul       | 2016-06-10 08:15:57 | Naomi       | 2016-06-10 09:39:37 |
|  3 |       333 | b     | Naomi      | 2016-06-10 09:39:37 | Anne        | 2016-06-10 09:46:09 |
|  8 |       333 | c     | Anne       | 2016-06-10 09:46:09 |             |                     |
+----+-----------+-------+------------+---------------------+-------------+---------------------+

最佳答案

您可以使用join执行此操作。对于您拥有的数据：

update t join
       t t2
       on t2.id = t.id + 1
    set t.modified_by = t2.created_by,
        t.valid_to = t2.valid_from;

假定id与您的示例数据一样是连续的。

编辑：

如果值是顺序的，则可以使用子查询来获取前一个，然后将其用于join：

update t join
       (select t2.*,
               (select max(t3.id) from t t3 where t3.id < t2.id) as prev_id
        from t t2
       ) t2
       on t2.prev_id = t.id
    set t.modified_by = t2.created_by,
        t.valid_to = t2.valid_from;

关于mysql - MySQL填充所有valid_to和modified_by列，知道所有valid_from和created_by值，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/37746798/