mysql - 计算连续天数(连续天数)和当天的记录数

Question

以下代码摘自SO上的另一个问题。 Original Q&A

我想计算自今天以来有记录的连续天数(连续)以及今天创造了多少记录。我用它来发送通知。如果用户在同一天提交新记录，他们不应该收到第二次通知，告诉他们他们处于连续状态(他们在第一次提交当天的记录时就被告知)。

我尝试在 @streak 之前、第一个 SELECT 之后以及几乎所有看起来合理的地方添加一个 COUNT() 函数，但这个查询对我来说太复杂了，无法弄清楚。

SELECT streak + 1 as realStreak
FROM (
SELECT dt, 
@streak := @streak+1 streak, 
datediff(curdate(),dt) diff
FROM (
SELECT distinct date(dt) dt
FROM glucose where uid = 1
) t1
CROSS JOIN (SELECT @streak := -1) t2
ORDER BY dt desc
)
t1 where streak = diff
ORDER BY streak DESC LIMIT 1

http://sqlfiddle.com/#!9/45d386/1/0

上面的结果应该是:

realStreak | RecordsToday
3          |    3

Answer 1

只需为今天的检查添加一个子查询

SELECT streak + 1 as realStreak,cdt
FROM (
SELECT dt,
@streak := @streak+1 streak, 
datediff(curdate(),dt) diff
FROM (
SELECT distinct date(dt) dt
FROM gl where uid = 1
) t1
CROSS JOIN (SELECT @streak := -1) t2
ORDER BY dt desc
)t1
JOIN
(SELECT COUNT(CASE WHEN DATE(dt)=CURDATE() THEN 1 END) cdt FROM gl)x
where streak = diff
ORDER BY streak DESC LIMIT 1