mysql中两个连续行之间的差异

本文介绍了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;