mysql间隔秒数_MySQL 查询记录时间相邻的相同号码, 记录间隔小于指定秒数的记录...

#表结构

CREATE TABLE `tmp_20120710_3` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`desttermid` varchar(11) DEFAULT NULL,

logtime varchar(20) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=gbk

#排序

#导入结构相同的临时表

#从PK上保证相同号码记录时间相邻

INSERT INTO `tmp_20120710_4` (desttermid, logtime)

SELECT desttermid, log_time FROM `tmp_20120710_3` ORDER BY desttermid, logtime

#记录时间格式yyyyMMddHHmmss

#转换成以秒为单位的时间戳计算记录间隔

#对desttermid字段值相同、记录时间相邻的时间戳相减

SELECT a.desttermid, ABS(UNIX_TIMESTAMP(a.logtime) - UNIX_TIMESTAMP(b.logtime)) AS timediff,

a.logtime, b.logtime

FROM `tmp_20120710_4` a, `tmp_20120710_4` b

WHERE a.desttermid = b.desttermid AND a.id = b.id + 1 AND timediff < 30