The same number adjacent to the MySQL query record time. The record interval is less than the specified number of seconds # 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 # Sort www.2cto.com
# Import a temporary table with the same structure # ensure the same number record time from the PK is adjacent to insert into 'tmp _ 20120710_4 (desttermid, logtime) SELECT desttermid, log_time FROM 'tmp _ 20120710_3 'order by desttermid, logtime # The record time format is yyyyMMddHHmmss # converted to a timestamp in seconds to calculate the record interval # SELECT a for timestamp with the same desttermid field value and adjacent record time. desttermid, ABS (UNIX_TIMESTAMP (. logtime)-UNIX_TIMESTAMP (B. logtime) AS timediff,. logtime, B. logtime FROM 'tmp _ 20120710_4 'a, 'tmp _ 20120710_4 'B WHERE. desttermid = B. desttermid AND. id = B. id + 1 AND timediff <30