您可以使用mysql变量来计算条纹。基本上,当
isDone
是1,当
伊索多
为0或日期不连续。此查询将生成每天的连续记录值:
SELECT s.Date,
@streak := IF(Date = @last_date + INTERVAL 1 DAY AND isDone = 1, @streak+1, 1) AS streak,
@last_date := Date AS last_date
FROM status s
JOIN (SELECT @streak := 0, @last_date := '1900-01-01') i
ORDER BY s.Date
输出:
Date streak last_date
2018-10-01 1 2018-10-01
2018-10-02 2 2018-10-02
2018-10-03 3 2018-10-03
2018-10-04 4 2018-10-04
2018-10-10 1 2018-10-10
2018-10-15 1 2018-10-15
2018-10-16 1 2018-10-16
2018-10-18 1 2018-10-18
2018-10-19 2 2018-10-19
2018-10-20 3 2018-10-20
然后,您可以使用它作为子查询来确定某一天的连续记录,例如
SELECT Date, streak
FROM (SELECT s.Date,
@streak := IF(Date = @last_date + INTERVAL 1 DAY AND isDone = 1, @streak+1, 1) AS streak,
@last_date := Date AS last_date
FROM status s
JOIN (SELECT @streak := 0, @last_date := '1900-01-01') i
ORDER BY s.Date) s
WHERE s.Date = '2018-10-20'
输出:
Date streak
2018-10-20 3
Demo on dbfiddle
