mysql 连续签到天数_最大连续签到天数-sql

SELECT MIN(rq) as 起始日期

, MAX(rq) as 终止日期

, MAX(id1) - MIN(id1) + 1 as 持续天数

,id3 as 累计签到天数

,name

FROM (

SELECT datediff(rq,'2020-02-01' )id1

, (

SELECT COUNT(1)

FROM tmptable

WHERE rq <= a.rq and

type = '是'

) id2,

(

SELECT COUNT(1)

FROM tmptable

WHERE

type = '是'

) id3, (

SELECT MAX(rq)

FROM tmptable

WHERE rq < a.rq

and type = '是'

) rq2,rq,type,name

FROM tmptable a

where type = '是'

) a

GROUP BY a.id1 - a.id2

order by 持续天数 desc

limit 1