问题:
表内容如下
-----------------------------
ID LogTime
1 2008/10/10 10:00:00
1 2008/10/10 10:03:00
1 2008/10/10 10:09:00
2 2008/10/10 10:10:00
2 2008/10/10 10:11:00
......
-----------------------------
如何查询同一id登陆时间间隔不超过5分钟的所有记录
建表:
DROP TABLE IF EXISTS `logindetail`;
CREATE TABLE `logindetail` (
`id` int(11) NOT NULL auto_increment,
`userid` char(4) default NULL,
`logintime` datetime default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `logindetail` VALUES (1,'0001','2008-10-10 10:00:00');
INSERT INTO `logindetail` VALUES (2,'0002','2008-10-10 10:03:00');
INSERT INTO `logindetail` VALUES (3,'0002','2008-10-10 10:09:00');
INSERT INTO `logindetail` VALUES (4,'0002','2008-10-10 10:11:00');
INSERT INTO `logindetail` VALUES (5,'0001','2008-10-10 10:12:00');
INSERT INTO `logindetail` VALUES (6,'0001','2008-10-10 10:19:00');
INSERT INTO `logindetail` VALUES (7,'0001','2008-10-10 10:21:00');
INSERT INTO `logindetail` VALUES (8,'0003','2008-10-10 10:23:00');
INSERT INTO `logindetail` VALUES (9,'0001','2008-10-10 10:25:00');
INSERT INTO `logindetail` VALUES (10,'0001','2008-10-10 10:31:00');
INSERT INTO `logindetail` VALUES (11,'0001','2008-10-10 10:33:00');
INSERT INTO `logindetail` VALUES (12,'0001','2008-10-10 10:38:00');
Sql:
SELECT * FROM logindetail;
SELECT a.id AS id, a.userid AS userid, a.logintime AS logintime
FROM logindetail AS a, logindetail AS b
WHERE a.userid = b.userid
AND a.id != b.id
AND DATEDIFF(a.logintime, b.logintime) = 0
AND HOUR(TIMEDIFF(b.logintime, a.logintime)) = 0
AND MINUTE(TIMEDIFF(b.logintime, a.logintime)) < 5
GROUP BY a.id ORDER BY a.id;