Mysql如何查询连续的时间次数

原创 2015年11月19日 13:39:33

在网上看到一道有意思的题目,大意是如何在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
                 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 login_day
          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

   结果为:

   

   看了下可以这么做,连续日期去最大的days,开始时间,结束时间去login_day,而是这样写了:

SELECT uid, max(days) lianxu_days, min(login_day) start_date,max(login_day) end_date 
  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 login_day
          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, cont_ix;

   结果是:

   

    这里存在的问题是:表里面的的sts登录时间不能有2条uid相同时间在同一天内。

    解决方法是:在case中添加一个<1 的判断条件

  

SELECT uid, max(days) lianxu_days, min(login_day) start_date,max(login_day) end_date 
  FROM (SELECT uid,
               @cont_day :=
               (CASE
                 WHEN (@last_uid = uid AND DATEDIFF(login_dt, @last_dt)=1) THEN
                  (@cont_day + 1)
                 WHEN (@last_uid = uid AND DATEDIFF(login_dt, @last_dt)<1) THEN
                  (@cont_day + 0)
                 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 login_day
          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, cont_ix;

   存在的问题:

   时间sts的时分秒不见了。

   

   欢迎各位留下更好的查询SQL,如本文中的SQL有问题也请指出,谢谢。

   全文完。    

相关文章推荐

Mysql如何查询连续时间内的次数

转自:http://www.tuicool.com/articles/uyEZJf 在网上看到一道有意思的题目,大意是如何在mysql查询连续在线的天数。 所谓的连续在线是指相邻两天都登录过,不...

MySql查询时日期补全(连续)的一种方法

MySql查询时日期补全(连续)的一种方法某一日,需要查询订单表中前7天每天的订单数,sql依次写出来:SELECT DATE_FORMAT(order_time,'%Y-%m-%d') da...

mysql 统计连续登录天数

在网上看到一道有意思的题目,大意是如何在mysql查询连续的时间内登录的次数。原文链接:      http://www.oschina.net/question/573517_118821 ...

mysql查询日期补全(连续)方法

SELECT od.date, od.orderCount FROM ( SELECT DATE_FORMAT(order_time,'...

MYSQL 不依赖其他表构建日期范围内的连续月份或日期

MYSQL通过存储过程查询指定编号纪录连续月份的明细纪录。 直接构建连续月份纪录, 不创建月份临时表...

Mybatis传多个参数(三种解决方案)

Mybatis的Mapper接口的参数,一般是一个对象,但如果不是对象,并且有多个参数的时候呢?我们第一个的想法是把参数封装成一个java.util.Map类型,然后在方法的注释上面写上map的key...

Mybatis打印可执行mysql语句(工具和拦截器两种方式)

1.使用Java程序快速将参数填充到sql语句中生成可执行sql语句 2.使用拦截器快速打印出完整的可执行sql语句...

mysql查询连续时间数据——无数据补0

用D3来画统计图,数据是从mysql数据库中查出来的。 统计图是关于某段时间内,每年/每月/每周的微博数量的折线图。数据表是一条微博为一条记录,每条记录有日期这个字段。 首先考虑用group by来统...

找出字符串中连续出现次数最多的子串

第一种解法来自:http://blog.csdn.net/tianshuai11/article/details/7797897, 大概时间复杂度为O(n^2logn) [cpp]...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Mysql如何查询连续的时间次数
举报原因:
原因补充:

(最多只允许输入30个字)