mysql中两个连续行之间的差异 [英] difference between two consecutive rows in mysql

问题描述

我在MySQL数据库中有一个表:

I have a table in MySQL database:

  id     ts            number
1   07-10-2017 00:00    200
2   07-10-2017 00:01    300
3   07-10-2017 00:02    700
4   07-10-2017 00:03    1000

我想计算两个连续行之间的差，并且我需要输出格式如下:

I want to calculate difference between two consecutive rows and i need output format be like:

id        ts           number   o/p
1   07-10-2017 00:00    200      0
2   07-10-2017 00:01    300     100
3   07-10-2017 00:02    700     400
4   07-10-2017 00:03    1000    300

我希望o/p列的第一个值为0，其余为差.

I want first value of o/p column to be 0 and rest is the difference.

任何帮助将不胜感激.

推荐答案

您可以使用局部变量来完成此操作:

You can do this with a local variable:

SET @tmp := -1;
SELECT id,
 ts ,
 number,
 if(@tmp =-1, 0, number - @tmp) as 'o/p',
 @tmp:=number as dummy
FROM YourTable
Order by ts;

这篇关于mysql中两个连续行之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！