php - mysql 查询一个用户连续签到天数

Question

我有个平台是有签到功能的，我需要判断这个用户连续在这个平台上签到多少天了 给他不同的积分。
例如今天是4月26号，从4月15号开始每天都签到了，那就是连续签到12天，4月13号也签到了，但是4月14号没签到，
mysql语句怎么写呢

Answer 1

Generally, the number of days of continuous check-in is stored. If it is not stored, one way we have done before is to store the check-in with a timestamp and then get the timestamp of the check-in. Generally, we get it for one month (if this month When the sign-in is full, get the previous month's number), and then loop through the number of days in PHP to calculate it. Later, this method was slower, so we recorded the check-in days separately

Answer 2

Database design issues.
Because it is necessary to count consecutive days, it is impossible to record one item every day (imagine a bank recording the user's balance for any number of days).
The accounting method is called the annual accumulation algorithm. That is, recording a certain point in time until another time. The balance at one point in time is unchanged (continuous)
The table design is roughly as follows:

End time-start time = number of days. Points conversion is just like tax, just calculate the bonus.

Answer 3

In your business rules, there should be an upper limit on how many points will be given for x consecutive days. For example, how many points will be given if there are >= 20 consecutive days.

Then let’s remember this x, in db select * from tb where uid = 10086 order by dt desc limit x

Then judge the maximum number of consecutive days in the program and add the corresponding score.