Mysql中连续行之间的差异

Question

我想得到MySql中两个连续行之间的差值。我正在试着解决，但没有成功。以下是图像中的数据

​

​

我需要在"Data2“列和"Diff”列的结果行之间的差异。

感谢您的关注和帮助。

-Ram

Answer 1

如果表有一个自动增量列' id '，我们可以按id排序，识别下一个行值并减去它

SELECT t1.*, t1.Data2-(SELECT t2.Data2 FROM  `table_name`  t2 WHERE t2.id > t1.id LIMIT 1 ) AS difference
FROM `table_name`  t1
ORDER BY t1.id

从下一行的值中减去

SELECT t1.*, t1.Data2-(SELECT t2.Data2 FROM  `table_name`  t2 WHERE t2.id < t1.id ORDER BY id DESC  LIMIT 1 ) AS difference
FROM `table_name`  t1
ORDER BY t1.id

Answer 2

由于表中没有惟一的列，因此可以通过包含绑定变量@rn & @rn1来实现这一点，该变量按顺序向表中的每一行添加一个惟一的数字。

试试这个：

SELECT tab1.application_id, tab1.fiscal_year, tab1.data1, coalesce(cast(tab1.data2 as signed) - 
                cast(tab2.data2 as signed), tab1.data2) as diff
  FROM 
      (SELECT b.application_id, @rn1:=@rn1+1 AS rank, b.fiscal_year, b.data1, b.data2
         FROM your_table b, (SELECT @rn1:=0) t1) as tab1,
      (SELECT a.application_id, @rn:=@rn+1 AS rank, a.fiscal_year, a.data1, a.data2
         FROM your_table a, (SELECT @rn:=0) t2) as tab2
 WHERE tab1.rank = tab2.rank + 1;