mysql计算连续次数_mysql一个连续日期（天）次数的统计

首先声明参考：http://www.oschina.net/question/573517_118821

表定义

CREATE TABLE `date_add` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`uid` int(11) NOT NULL DEFAULT '0',

`date` date NOT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `idx_ud` (`uid`,`date`)

) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=latin1

插入语句

INSERT INTO `date_add` (`id`, `uid`, `date`) VALUES

(1, 1, '2015-09-01'),

(2, 1, '2015-09-02'),

(17, 1, '2015-09-03'),

(18, 1, '2015-09-04'),

(3, 2, '2015-09-01'),

(4, 2, '2015-09-02'),

(19, 2, '2015-09-03'),

(20, 2, '2015-09-04'),

(5, 3, '2015-09-01'),

(6, 3, '2015-09-02'),

(21, 3, '2015-09-03'),

(22, 3, '2015-09-04'),

(7, 4, '2015-09-01'),

(8, 4, '2015-09-02'),

(9, 5, '2015-09-01'),

(10, 5, '2015-09-02'),

(11, 6, '2015-09-01'),

(12, 6, '2015-09-02'),

(13, 7, '2015-09-01'),

(14, 7, '2015-09-02'),

(15, 8, '2015-09-01'),

(16, 8, '2015-09-02');

表如下

iduiddate

112015-09-01

212015-09-02

1712015-09-03

1812015-09-05

322015-09-01

422015-09-02

1922015-09-03

2022015-09-04

532015-09-01

632015-09-02

2132015-09-03

2232015-09-04

742015-09-01

842015-09-02

952015-09-01

1052015-09-02

1162015-09-01

1262015-09-02

1372015-09-01

1472015-09-02

1582015-09-01

1682015-09-02

首先，sql如下

select uid,`date`,@countday:=(case when(@last_uid:=uid and DATEDIFF(`date`,@last_date)=1)then (@countday+1)else 1 end ) as countday ,(@group_id:=(@group_id+if(@countday=1,1,0))) asgroup_id,@last_uid:=uid as last_uid,@last_date:=`date` as last_date from (select `uid`,`date` from date_add order by uid,`date` )as t1,(select@countday:=0,@group_id:=0,@last_uid:='',@last_date:='') as t2

select uid,`date`,@countday:=(case when(@last_uid:=uid and DATEDIFF(`date`,@last_date)=1)then (@countday+1)else 1 end ) as countday ,(@group_id:=(@group_id+if(@countday=1,1,0))) asgroup_id,@last_uid:=uid as last_uid,@last_date:=`date` as last_date from (select `uid`,`date` from date_add order by uid,`date` )as t1,(select@countday:=0,@group_id:=0,@last_uid:='',@last_date:='') as t2

结果

uiddatecountdaygroup_idlast_uidlast_date

12015-09-011112015-09-01

12015-09-022112015-09-02

12015-09-033112015-09-03

12015-09-051212015-09-05

22015-09-011322015-09-01

22015-09-022322015-09-02

22015-09-033322015-09-03

22015-09-044322015-09-04

32015-09-011432015-09-01

32015-09-022432015-09-02

32015-09-033432015-09-03

32015-09-044432015-09-04

42015-09-011542015-09-01

42015-09-022542015-09-02

52015-09-011652015-09-01

52015-09-022652015-09-02

62015-09-011762015-09-01

62015-09-022762015-09-02

72015-09-011872015-09-01

72015-09-022872015-09-02

82015-09-011982015-09-01

82015-09-022982015-09-02

然后，sql

select uid,min(date) as mindate , max(date) as maxdate,max(countday) countday,group_concat(date) dates from ( select uid,`date`,@countday:=(case when(@last_uid:=uid andDATEDIFF(`date`,@last_date)=1)then (@countday+1)else 1 end ) as countday ,(@group_id:=(@group_id+if(@countday=1,1,0))) as group_id,@last_uid:=uid as last_uid,@last_date:=`date` aslast_date from (select `uid`,`date` from date_add order by uid,`date` )as t1,(select @countday:=0,@group_id:=0,@last_uid:='',@last_date:='') as t2) as t3 group by group_id

select uid,min(date) as mindate , max(date) as maxdate,max(countday) countday,group_concat(date) dates from ( select uid,`date`,@countday:=(case when(@last_uid:=uid andDATEDIFF(`date`,@last_date)=1)then (@countday+1)else 1 end ) as countday ,(@group_id:=(@group_id+if(@countday=1,1,0))) as group_id,@last_uid:=uid as last_uid,@last_date:=`date` aslast_date from (select `uid`,`date` from date_add order by uid,`date` )as t1,(select @countday:=0,@group_id:=0,@last_uid:='',@last_date:='') as t2) as t3 group by group_id

最终结果(countday 连续天数)

uidmindatemaxdatecountdaydates

12015-09-012015-09-0332015-09-01,2015-09-02,2015-09-03

12015-09-052015-09-0512015-09-05

22015-09-012015-09-0442015-09-03,2015-09-04,2015-09-01,2015-09-02

32015-09-012015-09-0442015-09-03,2015-09-04,2015-09-01,2015-09-02

42015-09-012015-09-0222015-09-02,2015-09-01

52015-09-012015-09-0222015-09-01,2015-09-02

62015-09-012015-09-0222015-09-01,2015-09-02

72015-09-012015-09-0222015-09-01,2015-09-02

82015-09-012015-09-0222015-09-01,2015-09-02