mysql如何查询两个日期之间最大的连续登录天数

前言

最近工作中遇到一个需求,是根据用户连续记录天数来计算的,求出用户在一段时间内最大的连续记录时间,例如在 2016-01-01 和 2016-01-28 之间,如果用户在3号和4号都记录了,那么连续记录天数为2,如果用户在6号-10号每日都记录了,那么最大连续记录天数为5.

拿到这个需求的时候,说实话有点懵,第一想到的就是在代码中去统计,会用到循环,想到那么多个用户,并且时间跨度也有点大,比如15年到16年,两年时间,想想就有点恐怖。

解决方案

然后就把这个需求跟朋友说了,朋友也觉得有点难搞,后来通过网上一篇文章有了一些小思路。但是看得也是一知半解的,虽然经常写 sql 语句,但也是常用的那些增删改查,像这样使用的方式根本没用过,过了会,朋友又扔给我一条 sql 语句,就在该文章的基础上进行了修改,以符合我的项目需求的语句。

SELECT *
FROM (SELECT *
   FROM (
       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(created_ts, @last_dt) = 1)
            THEN
             (@cont_day + 1)
           WHEN (@last_uid = uid AND DATEDIFF(created_ts, @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 := created_ts                login_day
          FROM (SELECT
              uid,
              DATE(created_ts) created_ts
             FROM plan_stage
             WHERE uid != 0
             ORDER BY uid, created_ts) AS t,
           (SELECT
            @last_uid := '',
            @last_dt := '',
            @cont_ix := 0,
            @cont_day := 0) AS t1
         ) AS t2
       GROUP BY uid, cont_ix
       HAVING lianxu_days > 10
      ) tmp
   ORDER BY lianxu_days DESC) ntmp
GROUP BY uid;

查询出来的结果如下图所示:

如果要查看单个人的,那么将 sql 语句中的 uid !=0 改成具体的值即可。

总结

以上就是这篇文章的全部内容了,希望本文的内容对大家学习或者使用sql语句能有所帮助,如果有疑问大家可以留言交流。

时间: 2016-10-20

mysql 查询指定日期时间内sql语句实现原理与代码

在数据库教程设计时必须注意时间字段为int(11)这样,保存在数据库的是一个数字型日期时间戳,我们可以用mktime函数求出当前日期的时间戳进行加减就OK了,下面看实例 //一个月 复制代码 代码如下: $lastMonth = mktime(date('h'),date('i'),date('s'),date('m')-1,date('d'),date('y')); $where .= " dtime > $lastMonth"; //三个月 复制代码 代码如下: $lastT

mysql中取系统当前时间,当前日期方便查询判定的代码

php中常通过下面的代码,得到判定日期的sql查询语句 复制代码 代码如下: $now = time(); //获取当期的日期 $sql="select * from `team` where end_time>$now ORDER BY sort_order limit 0,4"; 获取当前时间的MySql时间函数 处理MySql时间日期的函数有很多,下面为您介绍的就是用于获取当前时间的MySql时间函数,如果您对此感兴趣的话,不妨一看 下面为您介绍的MySql时间函数用于获取

MySQL查询两个日期之间记录的方法

网上搜索出来的结果多是下面答案: MySQL中,如何查询两个日期之间的记录,日期所在字段的类型为datetime(0000-00-00 00:00:00) 解决方案: 直接使用><=就可以查询. where createDate<'2003-5-31' and createDate>'2003-2-30'; 其实简单美好的写法可以是这样的 where createDate between'2010-08-01'  and  '2010-08-19' 看完了之后,你是不是觉得后者比较

MySql日期查询语句详解

使用DATE_FORMAT方法SELECT * FROM `ler_items` WHERE DATE_FORMAT(postTime,'%Y-%m')='2013-03'注意:日期一定要用'',否则没有效果其它的一些关于mysql日期查找语句mysql> select date_format(DATE_SUB(CURDATE(), INTERVAL 7 DAY),'%y%m%d');+-------------------–+| date_format(DATE_SUB(CURDATE(),

MySQL日期加减函数详解

1. addtime() 为日期加上指定秒数 select addtime(now(),1); -- 加1秒 2. adddate() 有两种用法,第二个参数直接填数字的话是为日期加上指定天数,填interval的话是为日期加上指定的interval时间 select adddate(now(),1); -- 加1天 select adddate(now(), interval 1 day); -- 加1天 select adddate(now(), interval 1 hour); --加1

优化MySQL数据库中的查询语句详解

很多时候基于php+MySQL建立的网站所出现的系统性能瓶颈往往是出在MySQL上,而MySQL中用的最多的语句就是查询语句,因此,针对MySQL数据库查询语句的优化就显得至关重要!本文就此问题做出详细分析如下: 1.判断是否向MySQL数据库请求了不需要的数据,如下列情况: (1).查询不需要的数据,例如你需要10条数据,但是你选出了100条数据加了limit做限制. (2).多表关联时返回全部列 (3).总是取出全部列select*......取出全部列,会让优化器无法完成索引覆盖扫描这类优

MySQL嵌套查询实例详解

本文实例分析了MySQL嵌套查询.分享给大家供大家参考,具体如下: MySQl从4.11版后已经完全支持嵌套查询了,那么下面举些简单的嵌套查询的例子吧(源程序来自MySQL User Manual): 1. SELECT语句的子查询 语法: 复制代码 代码如下: SELECT ... FROM (subquery) AS name ... 先创建一个表: CREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT); INSERT INTO t1 VALUES (

mysql慢查询使用详解

1 慢查询定义 指mysql记录所有执行超过long_query_time参数设定的时间阈值的SQL语句.慢查询日志就是记录这些sql的日志. 2 开启慢查询日志 找到mysql配置文件my.cnf.在mysqld的下面添加 复制代码 代码如下: log-slow-queries = D:/MySQL/log/mysqld-slow-query.log  #日志存在的位置.(注意权限的问题,可以不用设置,系统会给一个缺省的文件host_name-slow.log) long-query-time

Mysql自连接查询实例详解

本文实例讲述了Mysql自连接查询.分享给大家供大家参考,具体如下: 自连接查询 假想以下场景:某一电商网站想要对站内产品做层级分类,一个类别下面有若干子类,子类下面也会有别的子类.例如数码产品这个类别下面有笔记本,台式机,智能手机等:笔记本,台式机,智能手机又可以按照品牌分类:品牌又可以按照价格分类,等等.也许这些分类会达到一个很深的层次,呈现一种树状的结构.那么这些数据要怎么在数据库中表示呢?我们可以在数据库中创建两个字段来存储id和类别名称,使用第三个字段存储类别的子类或者父类的id,最后

MySQL连接查询实例详解

本文实例讲述了MySQL连接查询.分享给大家供大家参考,具体如下: 创建表suppliers: CREATE TABLE suppliers ( s_id int NOT NULL AUTO_INCREMENT, s_name char(50) NOT NULL, s_city char(50) NULL, s_zip char(10) NULL, s_call CHAR(50) NOT NULL, PRIMARY KEY (s_id) ) ; INSERT INTO suppliers(s_i

mysql常用日期时间/数值函数详解(必看)

1.日期时间函数 时间转化秒函数:time_to_sec MySQL> select time_to_sec('01:01:01'); +-------------------------+ | time_to_sec('01:01:01') | +-------------------------+ | 3661 | +-------------------------+ 1 row in set (0.00 sec) 秒转化时间函数:sec_to_time mysql> select se

MySql中子查询内查询示例详解

西北望乡何处是,东南见月几回圆. 月亮又慢悠悠的挂上了天空,趁着睡前梦呓,我就带领各位可爱的读者们探索MySql最后的子查询部分. 说明:有些查询结果出来结果截图与题目要求不一样会出现多余的字段是为了方便展示结果的可读性.实际操作的读者可以删除SELECT后面多余的字段得到正确的结果. #WHERE或HAVING后面 #1.标量子查询(单行子查询) #2.列子查询(多行子查询) #3.行子查询(多列多行) #特点: # ①子查询放在小括号内 # ②子查询一般放在条件的右侧 # ③标量子查询:一般

查看当前mysql使用频繁的sql语句(详解)

#mysql -uroot -p 输入密码 mysql> show full processlist;    查看完全的SQL语句 mysql> show processlist;         查看整体情况 这样子可以针对SQL语句进行优化. 以上这篇查看当前mysql使用频繁的sql语句(详解)就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持我们.