连续签到，只计算连续的天数，断掉则为0重新计算

  1.获取到今天和昨天的时间,如果发现今天或昨天没有签到记录,则 连续签到为0
  2.如果今天或昨天发现签到记录,根据签到日期倒序,然后生成最大(最近)的签到时间 - 1天的自然日,将两列日期进行对比,得到连续签到天数 

SELECT
count(1)
FROM
(
SELECT
date_sub(a.create_date, INTERVAL 1 DAY) signDate,
(
@i := DATE_ADD(@i, INTERVAL - 1 DAY)
) today
FROM
(
SELECT
create_date
FROM
active_task_detail
WHERE
user_id = #{userId}
ORDER BY
create_date DESC
) a
INNER JOIN (
SELECT
@i := max(create_date) AS signMax
FROM
active_task_detail
WHERE
user_id = #{userId}
AND (
TO_DAYS(create_date) = TO_DAYS(curdate())
OR TO_DAYS(create_date) = TO_DAYS(
DATE_ADD(curdate(), INTERVAL - 1 DAY)
)
)
) b ON b.signMax IS NOT NULL
AND TO_DAYS(DATE_ADD(@i, INTERVAL - 1 DAY)) = TO_DAYS(
date_sub(a.create_date, INTERVAL 1 DAY)
)
) c

---

作者：来世还生华夏门
来源：CSDN
原文：https://blog.csdn.net/qq_38708432/article/details/96424805
版权声明：本文为博主原创文章，转载请附上博文链接！