mysql让两行相减

Question

原表:

+----+-------------+------------------+---------------------+
| id | playnum_sum | real_playnum_sum | create_time         |
+----+-------------+------------------+---------------------+
|  1 |    100|                300| 2015-05-05 19:54:01 | 
|  2 |    200|                400| 2015-05-06 19:54:01 | 
|  3 |    300|                500| 2015-05-07 16:09:04 | 
+----+-------------+------------------+---------------------+

我想让每两行相减，结果是这样的:

计算表:

+----+-------------+------------------+---------------------+
| id | playnum_sum | real_playnum_sum | create_time         |
+----+-------------+------------------+---------------------+
|  1 |    100|100| 2015-05-06 19:54:01     (this is the second time in Origin table) | 
|  2 |    100|100| 2015-05-07 16:09:04    (this is the third time in Origin table) | 
+----+-------------+------------------+---------------------+

请注意创建时间。如何编写sql？

Answer 1

如果您的 ID 没有间隔，您可以通过自连接来实现:

select oprev.id, o.playnum_sum - oprev.playnum_sum,
       o.real_playnum_sum - oprev.real_playnum_sum,
       o.create_time
from original o join
     original oprev
     on o.id = oprev.id + 1;