SET @num:=0; #开始日期

SET    @type:="";  # 存放useid

SET @lastdate:="2018-01-01";

SET  @b=0;

SELECT    userid

,logdate

,@lastdate,

@b:=IF(@type=userid ,DATEDIFF(logdate,@lastdate),0) as  num2

,@type:=userid

,@lastdate:=logdate

FROM

(

SELECT  id as userid ,date(date ) as logdate

FROM  20180704rolelogin

GROUP BY    id ,date(date )

) as a ;

3.计算连续天数和累计付费金额

可用代码：

SET @a:='2018-05-03' , @b:='',@c:=0; #定义临时变量

SELECT  name ,a  as startdate,MAX(date) as enddate,sum(pay) as money

FROM

#核心部分

(SELECT  name,@a:=IF((DATEDIFF(date,@a)=@c)and(@b=name),@a,date) as a ,

#如果日期连续就保持不变，否则重置为当前日期

date,pay,@b:=NAME #作为辅助列区分不同的name

,@c:=DATEDIFF(date,@a)+1 #日期差加1作为，下一个日期的判断标准

FROM  xiaozhan as  a ) as a

GROUP BY  name,a