MySQL函数获取连续签到天数

MySQL函数获取连续签到天数

最近项目中有需要统计连续签到天数，结合其他大佬的sql函数修改的。之前拿其他大佬的时候发现有些小bug，统计的时间有些问题 比如断签之后显示的会是断签前的连续签到天数，重新签到之后才能计算当前的签到天数。
表：

CREATE TABLE `t_sign_record` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `UserId` int(11) DEFAULT NULL COMMENT '用户id',
  `CreateTime` datetime DEFAULT NULL COMMENT '创建时间',
  `SignDate` date DEFAULT NULL COMMENT '签到日期',
  `recordType` tinyint(5) DEFAULT NULL COMMENT '签到类型:1,正常;2,补签',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8mb4

一下是sql函数

DELIMITER $$
DROP FUNCTION IF EXISTS `f_continuty_days`$$
CREATE DEFINER=`root`@`%` FUNCTION `f_continuty_days`(
  id INT,
  start_time DATE,
  end_time DATE
) RETURNS INT(11)
BEGIN
  DECLARE days INT ;
  SELECT 
    IFNULL(days_count, 0)  INTO days 
  FROM
    (SELECT 
      * 
    FROM
      (SELECT 
        UserId,
        @cont_day := (
          CASE
            WHEN (
              @last_uid = UserId 
              AND DATEDIFF(ot, @last_ot) = 1
            ) 
            THEN (@cont_day + 1) 
            WHEN (
              @last_uid = UserId 
              AND DATEDIFF(ot, @last_ot) < 1
            ) 
            THEN (@cont_day + 0) 
            ELSE 1 
          END
        ) AS days_count,
        (
          @cont_ix := (@cont_ix + IF (@cont_day = 1, 1, 0))
        ) AS cont_ix,
        @last_uid := UserId,
        @last_ot := ot ot 
      FROM
        (SELECT 
          UserId,
          SignDate AS ot 
        FROM
          t_sign_record 
        WHERE UserId = id 
        ORDER BY UserId,
          SignDate) AS t1,
        (SELECT 
          @last_uid := '',
          @last_ot := '',
          @cont_ix := 0,
          @cont_day := 0) AS t2) p 
    WHERE '2018-01-01' <= p.ot 
      AND DATE_FORMAT(p.ot, '%Y%m%d')  >= DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 DAY), '%Y%m%d')) t 
  WHERE DAY(ot) = DAY(NOW()) 
    OR DAY(ot) = DAY(DATE_SUB(NOW(), INTERVAL 1 DAY)) 
  ORDER BY ot DESC 
  LIMIT 1 ;
  RETURN days ;
END$$

DELIMITER ;