First DECLARE reference: http://www.oschina.net/question/573517_118821
Table Definition
CREATE TABLE ' Date_add ' (
' id ' int (one) not NULL auto_increment,
' uid ' int (one) not NULL DEFAULT ' 0 ',
' Date ' date is not NULL,
PRIMARY KEY (' id '),
UNIQUE KEY ' idx_ud ' (' uid ', ' date ')
) Engine=innodb auto_increment=23 DEFAULT charset=latin1
Insert statement
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 ');
Table below
ID UID date
1 1 2015-09-01
2 1 2015-09-02
1 2015-09-03
1 2015-09-05
3 2 2015-09-01
4 2 2015-09-02
2 2015-09-03
2 2015-09-04
5 3 2015-09-01
6 3 2015-09-02
3 2015-09-03
3 2015-09-04
7 4 2015-09-01
8 4 2015-09-02
9 5 2015-09-01
5 2015-09-02
6 2015-09-01
6 2015-09-02
7 2015-09-01
7 2015-09-02
8 2015-09-01
8 2015-09-02
First, SQL follows
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, ([Email protected]:=0, @group_id: =0,@ Last_uid:= ', @last_date: = ') as t2
Select UID, ' Date ', @countday: = (case if (@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, ([email protected]:=0, @group_id: =0, @last_u Id:= ", @last_date: =") as T2
Results
UID date countday group_id last_uid last_date
1 2015-09-01 1 1 1 2015-09-01
1 2015-09-02 2 1 1 2015-09-02
1 2015-09-03 3 1 1 2015-09-03
1 2015-09-05 1 2 1 2015-09-05
2 2015-09-01 1 3 2 2015-09-01
2 2015-09-02 2 3 2 2015-09-02
2 2015-09-03 3 3 2 2015-09-03
2 2015-09-04 4 3 2 2015-09-04
3 2015-09-01 1 4 3 2015-09-01
3 2015-09-02 2 4 3 2015-09-02
3 2015-09-03 3 4 3 2015-09-03
3 2015-09-04 4 4 3 2015-09-04
4 2015-09-01 1 5 4 2015-09-01
4 2015-09-02 2 5 4 2015-09-02
5 2015-09-01 1 6 5 2015-09-01
5 2015-09-02 2 6 5 2015-09-02
6 2015-09-01 1 7 6 2015-09-01
6 2015-09-02 2 7 6 2015-09-02
7 2015-09-01 1 8 7 2015-09-01
7 2015-09-02 2 8 7 2015-09-02
8 2015-09-01 1 9 8 2015-09-01
8 2015-09-02 2 9 8 2015-09-02
Then, the 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: = (@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 (the Select ' UID ', ' Date ' from the 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
Final result (countday consecutive days)
UID mindate maxdate countday dates
1 2015-09-01 2015-09-03 3 2015-09-01,2015-09-02,2015-09-03
1 2015-09-05 2015-09-05 1 2015-09-05
2 2015-09-01 2015-09-04 4 2015-09-03,2015-09-04,2015-09-01,2015-09-02
3 2015-09-01 2015-09-04 4 2015-09-03,2015-09-04,2015-09-01,2015-09-02
4 2015-09-01 2015-09-02 2 2015-09-02,2015-09-01
5 2015-09-01 2015-09-02 2 2015-09-01,2015-09-02
6 2015-09-01 2015-09-02 2 2015-09-01,2015-09-02
7 2015-09-01 2015-09-02 2 2015-09-01,2015-09-02
8 2015-09-01 2015-09-02 2 2015-09-01,2015-09-02
MySQL statistics for a continuous date (day) Count