MySQL查询连续数据

查询连续记录并对这些连续数据统计取出指定连续次数的记录，这类操作并不多，但出现时会比较棘手。

查询思想是：

顺序行号 - 减首差值 = 连续差块

顺序行号 如同 Oracle 中的 rownum 但MySQL目前还没有这个功能，所以只能通过局部变量来实现，

减首差值 就是每条记录与最开始记录的差（需要保证这个差值与顺序行号递增值相同，当然如果本来就是自增值则不需要单独计算）

只要 顺序行号与减首差值保持相同递增值则 连续差块 值相同，就可以统计出连续长度

示例表：（以简单的签到表为例）

create table user_sign( id int unsigned primary key auto_increment, user_id int unsigned not null comment '用户ID', date date not null comment '签到日期', created_time int unsigned not null comment '创建时间', updated_time int unsigned not null comment '修改时间' )engine=innodb default charset=utf8 comment '用户签到';

随机生成数据（创建函数随机生成签到数据）

create function insert_sign_data(num int) returns int begin declare _num int default 0; declare _date date; declare _tmpdate date; declare _user_id int; declare line int default 0; declare _get_last cursor for select date from user_sign where user_id=_user_id order by date desc limit 1; declare continue handler for SQLSTATE '02000' set line = 1; while _num < num do set _user_id = CEIL( RAND( ) * 500 ); open _get_last; fetch _get_last into _tmpdate; IF line THEN set _date = FROM_UNIXTIME( unix_timestamp( ) - 86400 * round( RAND( ) * 200 ), '%Y-%m-%d' ); set line = 0; ELSE set _date = FROM_UNIXTIME( unix_timestamp( _tmpdate ) + 86400 * round( RAND( ) * 2 + 1), '%Y-%m-%d' ); END IF; INSERT INTO user_sign ( user_id, date, created_time, updated_time ) VALUES (_user_id, _date, unix_timestamp( ), unix_timestamp( )); set _num = _num + 1; close _get_last; end while; return _num; end

生成数据（由于生成时有判断最近打卡日期生成有会点慢）

select insert_sign_data(20000);

提取出连续打卡超过6天的用户

SELECT user_id, val - ( @rownum := @rownum + 1 ) AS type, group_concat( date ) AS date_join, count( 1 ) num FROM ( SELECT us1.date, us1.user_id, ( unix_timestamp( us1.date ) - min_timestamp ) / 86400 + 1 AS val FROM user_sign AS us1 LEFT JOIN ( SELECT UNIX_TIMESTAMP( min( date ) ) AS min_timestamp, user_id, min( date ) AS min_date FROM user_sign GROUP BY user_id ) AS us2 ON us1.user_id = us2.user_id ORDER BY us1.user_id ASC, us1.date ASC ) AS t1, ( SELECT @rownum := 0 ) AS t2 GROUP BY user_id, type HAVING num > 6

这里查询的是全表里连续超过3次打卡的，并把日期展示出来。

查询的思路是：

1.提取出全表用户每次打卡记录与第一次打卡记录的差值但按用户与日期正排序

2.增加一个局部变量rownum与上面查询数据进行连查

3.在结果字段集里使用日期差值减去自增顺序行号值得到连续差块

4.通过分组用户与连续差块获取连续签到次数

5.通过having来提取超过6次签到的用户