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优化：

优化原因1：方法1中的a.id = b.id+1进行查询速度很慢（1min以上），但a.id = b.id进行查询的速度很快；于是开始想到update id （UPDATE tabname  SET id =id-1 测试可行  ;）

优化原因2：消息序列在筛选时容易出现terminal_id 不一致现象，所以增加条件

优化原因3，查看计算结果的过程明细

优化后语句：

SET @arownum=0;
SET @brownum=1;

#查询sql

SELECT a.id ,b.id,a.terminal_id,a.TIMESTAMP,b.TIMESTAMP,a.seq,b.seq ,a.seq-b.seq AS dseq FROM
( SELECT @arownum:=@arownum+1 AS id ,terminal_id,TIMESTAMP,seq FROM `msg_basic` ORDER BY terminal_id, TIMESTAMP )AS a,
( SELECT @brownum:=@brownum+1 AS id ,terminal_id,TIMESTAMP,seq FROM `msg_basic` ORDER BY terminal_id, TIMESTAMP )AS b
WHERE a.id = b.id AND a.terminal_id=b.terminal_id #筛选设备相同、seq差距为1
AND a.seq-b.seq>1 #根据消息序号进行筛选，丢失消息数量需要大于1，如果连续则为1

AND LEFT(b.TIMESTAMP,6)=LEFT(a.TIMESTAMP,6)#筛选日期相同的，进行消息包减法

AND CAST(a.seq AS SIGNED INTEGER) > 12 #排除熄火后重新点火后seq变为1
AND CAST(a.timestamp AS SIGNED INTEGER) > 180201095138 #排除 18-02-01之前的数据
AND a.terminal_id <> '01020408101F' # 排除设备编号为01020408101F

 

方法2：通过limit不能实现原因

前提  t1=[2-6]5个数字，t2:=[1:6] 6个数字 ，t1为t2删去一行

因为mysql的相减 必须通过  where 条件找到共同的条件，类似于t1.id= t2.id;直接让其相减则能够得到, 5*6=30个结果；

 

 

写sql经验：逐步编写最简单的语句组成复杂的语句，逐步测试，才能逻辑清晰，调试容易