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
SELECT MIN(rq) as 本期起始日期
, MAX(rq) as 本期终止日期
, MAX(id1) - MIN(id1) + 1 as 持续天数
, CASE a.id1 - a.id2
WHEN -1 THEN 0
ELSE MAX(datediff( rq2, rq))
END as 距上一期天数
FROM (
SELECT datediff(rq,'2020-02-01' )id1
, (
SELECT COUNT(1)
FROM tmptable
WHERE rq <= a.rq and
type = '是'
) id2, (
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
| 
转播
