SELECT
uid,
min(date) AS mindate,
max(date) AS maxdate,
max(countday) countday,
group_concat(date) dates
FROM(
SELECT uid,date
,@countday := ( CASE WHEN ( DATEDIFF(date
,@last_date) = 1) THEN (@countday + 1) ELSE 1 END) AS countday,
@last_date := date
FROM(SELECT uid
,date
FROM test_demo ORDER BY uid,date) AS t1,
(select @countday:=0,@last_date:=’’) as t2
) AS t3 GROUP BY uid
SELECT DATEDIFF(‘2015-09-02’,‘2015-09-01’)
分解sql
SELECT uid
,date
FROM test_demo ORDER BY uid,date 排序查出id,日期
@countday @last_date 这种是变量声明
:= 赋值
@countday := ( CASE WHEN ( DATEDIFF(date
,@last_date) = 1) THEN (@countday + 1) ELSE 1 END
DATEDIFF(d1,d2) 计算两个时间的时间差值
当两个时间差值为1的时候,则为连续天数,
@countday