顺序行号——减首差值=连续差块,
顺序行号如oracle中同的rownum但mysql目前还没有这个功能,所以只能通过局部变量来实现,
减首差值就是每条记录与最开始记录的差(需要保证这个差值与顺序行号递增值相同,当然如果本来就是自增值则不需要单独计算)
只要顺序行号与减首差值保持相同递增值则连续差块值相同,就可以统计出连续长度
示例表:(以简单的签到表为例)
create table user_sign ( id int  unsigned primary key  auto_increment, user_id int  unsigned not null  comment & # 39;用户id # 39;, date date  not null  comment & # 39;签到日期& # 39;, created_time int  unsigned not null  comment & # 39;创建时间& # 39;, updated_time int  unsigned not null  comment & # 39;修改时间& # 39; )引擎=innodb default charset=utf8 comment & # 39;用户签到& # 39;;
随机生成数据(创建函数随机生成签到数据)
create function insert_sign_data (num int) int returns 开始 declare _num  int default 0; declare _date 日期; declare _tmpdate 日期; declare _user_id  int; declare line  int default 0; declare _get_last  cursor for select  date 得到user_sign where user_id=_user_id order by date desc limit 1; declare continue  handler for  SQLSTATE & # 39; 02000 & # 39;, set line =, 1; while _num & lt; num 做 set _user_id =,装天花板(,兰德(,)*,500,); open _get_last; fetch _get_last  into _tmpdate; IF line 然后 set _date =, FROM_UNIXTIME (, unix_timestamp(,),安康;86400,*,圆(,兰德(,)*,200,),,& # 39;Y - % - % d % # 39;,); set line =, 0; 其他的 set _date =, FROM_UNIXTIME (, unix_timestamp (, _tmpdate ), +, 86400, *,圆(,兰德(,)*,2,+,- 1),,& # 39;Y - % - % d % # 39;,); 最终获得;如果; INSERT INTO  user_sign (, user_id,日期,created_time,, updated_time ), VALUES (_user_id, _date,, unix_timestamp (,),, unix_timestamp (,)); set _num =, _num +, 1; close _get_last; 最终获得;; return _num; 结束
生成数据(由于生成时有判断最近打卡日期生成有会点慢)
select insert_sign_data (20000),
提取出连续打卡超过6天的用户
选择 user_id, val 作用;(,@rownum :=, @rownum +, 1,), AS 类型, group_concat (, date ), AS date_join, 计数(,1),num 从 ( 选择 us1.date, us1.user_id, (,unix_timestamp (, us1.date ),安康;min_timestamp ),/, 86400, +, 1, AS val 从 user_sign AS  us1 LEFT JOIN (, SELECT UNIX_TIMESTAMP (min (), date 大敌;;),AS min_timestamp,, user_id,, min (), date , AS min_date 得到user_sign GROUP BY user_id ), AS us2 提醒us1.user_id =, us2.user_id ORDER us1.user_id ASC, us1.date ASC ),AS t1, (,SELECT @rownum :=, 0,), AS t2 GROUP user_id, type 有 num 祝辞,6
这里查询的是全表里连续超过3次打卡的,并把日期展示出来。
查询的思路是:
- <李>
提取出全表用户每次打卡记录与第一次打卡记录的差值但按用户与日期正排序
李> <李>增加一个局部变量rownum与上面查询数据进行连查
李> <李>在结果字段集里使用日期差值减去自增顺序行号值得到连续差块
李> <李>通过分组用户与连续差块获取连续签到次数
李> <李>通过在来提取超过6次签到的用户
李>