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
#排序 www.2cto.com
#導入結構相同的臨時表
#從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
