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

bitsCN.com
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
bitsCN.com