mysql 获取前后相邻,MySQL如何计算相邻两行某列差值

MySQL计算相邻两行某列差值的方法：

首先博主在服务端有一个表来记录司机上报上来的GPS点位信息，表结构如下：

-- 司机GPS收集表

CREATE TABLE captainad_driver_gps_position (

id BIGINT NOT NULL auto_increment COMMENT '主键',

business_id BIGINT DEFAULT NULL COMMENT '业务ID',

device_mac VARCHAR (64) DEFAULT NULL COMMENT '设备MAC地址',

device_imei VARCHAR (64) DEFAULT NULL COMMENT '设备IMEI',

lat_lng VARCHAR (64) DEFAULT NULL COMMENT '纬经度',

capture_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '捕获时间',

create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',

update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',

PRIMARY KEY (id),

KEY `idx_business_id` (`business_id`) USING BTREE

) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT = '司机GPS收集';

表中记录的数据大致如下：

现在就对按获取GPS位置的时间capture_time在按照时间排序之后，进行前后两条记录计算差值。为了计算两者的差值，那么我们肯定是需要获取到一前一后两条记录的，这里我们可以巧用一个变量来记录当前行的行数，然后随着循环查询每次将行数叠加，以达到行记录的目的，这样一来，我们就能知道哪两条记录是一前一后的了。

打印行号的SQL语句：

SELECT

(@rownum := @rownum + 1) AS rownum,

tab.business_id,

tab.device_mac,

tab.capture_time

FROM

captainad_driver_gps_position tab,

(SELECT @rownum := 0) r -- 声明变量

WHERE

1 = 1

AND DATE_FORMAT(

tab.capture_time,

'%Y-%m-%d'

) = '2019-06-28'

ORDER BY

tab.capture_time

基于此，我们将目标SQL给写出来，这里我根据我们的实际业务将语句稍微做了整理，脚本大致如下：

SELECT

t.business_id,

t.device_mac,

t.capture_time,

t.tdiff

FROM

(

SELECT

r1.business_id,

r1.device_mac,

r1.capture_time,

TIMEDIFF(

r2.capture_time,

r1.capture_time

) AS 'tdiff'

FROM

(

SELECT

(@rownum := @rownum + 1) AS rownum,

tab.business_id,

tab.device_mac,

tab.capture_time

FROM

captainad_driver_gps_position tab,

(SELECT @rownum := 0) r

WHERE

1 = 1

AND DATE_FORMAT(

tab.capture_time,

'%Y-%m-%d'

) = '2019-06-28'

ORDER BY

tab.capture_time

) r1

LEFT JOIN (

SELECT

(@INDEX := @INDEX + 1) AS rownum,

tab.business_id,

tab.device_mac,

tab.capture_time

FROM

captainad_driver_gps_position tab,

(SELECT @INDEX := 0) r

WHERE

1 = 1

AND DATE_FORMAT(

tab.capture_time,

'%Y-%m-%d'

) = '2019-06-28'

ORDER BY

tab.capture_time

) r2 ON r1.business_id = r2.business_id

AND r1.device_mac = r2.device_mac

AND r1.rownum = r2.rownum - 1

) t

WHERE

t.tdiff > '00:00:15'