mysql 实现 上一行减去下一行

方法1 ：通过行号来进行加减

1.新建表1添加自增行号列（考虑到自增id 有丢失数据现象）

2.复制上表1为表2

3.根据表1、表2行号进行where或者left join on的处理

* where 进行处理的条件为表1表2完全的匹配

*left join on 处理的条件为  左表完全的匹配

#设置内存变量

SET @arownum=0;

SET @brownum=0;

#查询sql
SELECT a.id ,a.terminal_id,a.TIMESTAMP,a.seq-b.seq FROM
( SELECT @arownum:=@arownum+1 AS id ,terminal_id,TIMESTAMP,seq FROM `msg_basic` ORDER BY terminal_id, TIMESTAMP )AS a
LEFT JOIN
( SELECT @brownum:=@brownum+1 AS id ,terminal_id,TIMESTAMP,seq FROM `msg_basic` ORDER BY terminal_id, TIMESTAMP )AS b
ON a.id = b.id+1

#也可新建表

CREATE TABLE tab2 AS SELECT @brownum:=@brownum+1 AS id  ,terminal_id,TIMESTAMP,seq FROM `msg_basic` ORDER BY  terminal_id, TIMESTAMP 

 

方法1优化：