mysql - 减去当前行的下一个并将该输出作为当前行的数据

Question

计算公里跑是基于此

当前值 = 下一个值 - 当前值

我有一张看起来像这样的表格。

我的问题是如何根据里程表值计算 kmr？我将替换kmr值的kmr列的值

Answer 1

您可以使用变量来存储最后的值。

create table tbl (code varchar(10), vdate date, kmr int);

✓

insert into tbl values
('Person1', '20180101', 71883),
('Person1', '20180102', 71893),
('Person1', '20180103', 71903),
('Person2', '20180101', 71800),
('Person2', '20180102', 71815),
('Person2', '20180103', 71820);

✓

select code, vdate, kmr, current_kmr
from
(
  select t1.code, t1.vdate, t1.kmr, 
         t1.kmr - if(coalesce(@last_code, t1.code) = t1.code, coalesce(@last_kmr, t1.kmr), t1.kmr)  as current_kmr,
         @last_kmr := t1.kmr,
         @last_code := t1.code
  from   tbl t1,
         (select @last_kmr := null, @last_code := null) t2
  order by t1.code, t1.vdate
) t

code    | vdate      |   kmr | current_kmr:------ | :--------- | ----: | ----------:Person1 | 2018-01-01 | 71883 |           0Person1 | 2018-01-02 | 71893 |          10Person1 | 2018-01-03 | 71903 |          10Person2 | 2018-01-01 | 71800 |           0Person2 | 2018-01-02 | 71815 |          15Person2 | 2018-01-03 | 71820 |           5

dbfiddle here