在网上看到一道有意思的题目,大意是如何在mysql查询连续的时间内登录的次数。原文链接:
首先建表,填充测试数据:
CREATE TABLE `tmysql_test_lianxu_3` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`uid` int(11) DEFAULT NULL,
`sts` datetime DEFAULT NULL COMMENT '登录时间',
`ets` datetime DEFAULT NULL COMMENT '离线时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
测试数据为:
INSERT INTO `tmysql_test_lianxu_3` VALUES (1, 1, '2014-1-1 21:00:00', '2014-1-2 07:00:00');
INSERT INTO `tmysql_test_lianxu_3` VALUES (2, 1, '2014-1-2 15:37:57', '2014-1-2 21:00:00');
INSERT INTO `tmysql_test_lianxu_3` VALUES (3, 2, '2014-1-1 09:00:00', '2014-1-1 15:00:00');
INSERT INTO `tmysql_test_lianxu_3&