???? 在网上看到一道有意思的题目，大意是如何在mysql查询连续的时间内登录的次数。原文链接：

???? http://www.oschina.net/question/573517_118821

???? 首先建表，填充测试数据：

????

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` VALUES (4, 2, '2014-1-2 09:00:00', '2014-2-1 16:00:00');
INSERT INTO `tmysql_test_lianxu_3` VALUES (5, 1, '2014-1-4 10:00:00', '2014-1-4 18:00:00');
INSERT INTO `tmysql_test_lianxu_3` VALUES (6, 1, '2014-1-5 12:00:00', '2014-1-5 13:00:00');
INSERT INTO `tmysql_test_lianxu_3` VALUES (7, 2, '2014-1-10 00:00:00', '2014-1-10 06:00:00');
INSERT INTO `tmysql_test_lianxu_3` VALUES (8, 2, '2014-1-11 13:00:00', '2014-1-11 18:00:00');
INSERT INTO `tmysql_test_lianxu_3` VALUES (10, 2, '2014-1-12 12:00:00', '2014-1-12 18:00:00');
INSERT INTO `tmysql_test_lianxu_3` VALUES (11, 1, '2014-1-8 06:00:00', '2014-1-8 16:00:00');
INSERT INTO `tmysql_test_lianxu_3` VALUES (12, 2, '2014-1-11 21:00:00', '2014-1-12 06:00:00');

?? 在Oracle中可以使用row_number搞定，mysql中怎么做呢？

?? 可以参考链接：

??? http://www.explodybits.com/2011/11/mysql-row-number/?

??? 首先看原文中给出的答案：

???

SELECT uid, days, COUNT(*) AS num
  FROM (SELECT uid,
               @cont_day :=
               (CASE
                 WHEN (@last_uid = uid AND DATEDIFF(login_dt, @last_dt) = 1) THEN
                  (@cont_day + 1)
                 ELSE
                  1
               END) AS days,
               (@cont_ix := (@cont_ix + IF(@cont_day = 1, 1, 0))) AS cont_ix,
               @last_uid := uid,
               @last_dt := login_dt
          FROM (SELECT uid, DATE(sts) AS login_dt
                  FROM tmysql_test_lianxu_3
                 ORDER BY uid, sts) AS t,
               (SELECT @last_uid := '',
                       @last_dt  := '',
                       @cont_ix  := 0,
                       @cont_day := 0) AS t1) AS t2
 GROUP BY uid, days;

?? 也是使用了mysql模拟oracle的row_number函数。

?? 运行结果是:??

?

??

??? 我看了半天发现结果好像不是我想要的，我想要的是要有开始时间，结束时间之类的。

??? 看下中间表再说：

???

SELECT uid,
               @cont_day :=
               (CASE