MySQL 判断用户连续签到多少天

where条件

WHERE m_id = 4 and integral_type=1 and del_flag=0

表结构和数据

/*
 Navicat Premium Data Transfer

 Source Server         : 
 Source Server Type    : MySQL
 Source Server Version : 50562
 Source Host           : 
 Source Schema         : home_future_dev

 Target Server Type    : MySQL
 Target Server Version : 50562
 File Encoding         : 65001

 Date: 02/09/2020 23:02:43
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for home_integral_log
-- ----------------------------
DROP TABLE IF EXISTS `home_integral_log`;
CREATE TABLE `home_integral_log`  (
  `integral_id` bigint(22) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `m_id` bigint(22) NOT NULL COMMENT '会员id',
  `m_no` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '会员编号',
  `integral_type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '积分类型：1-签到，2-订单 ，3-其他',
  `settlement_type` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '结算类型1-支入 2-支出',
  `integral_value` int(7) NOT NULL DEFAULT 0 COMMENT '积分数额',
  `directions` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '说明',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `order_no` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '订单编号',
  `del_flag` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '删除标识0-正常，1-删除',
  PRIMARY KEY (`integral_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 15 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '积分记录表' ROW_FORMAT = COMPACT;

-- ----------------------------
-- Records of home_integral_log
-- ----------------------------
INSERT INTO `home_integral_log` VALUES (1, 4, '457848301915963392', '1', '1', 10, '签到发放', '2020-07-31 18:23:27', NULL, 0);
INSERT INTO `home_integral_log` VALUES (12, 4, '457406012030681088', '1', '1', 10, '签到发放', '2020-09-01 21:18:55', NULL, 0);
INSERT INTO `home_integral_log` VALUES (13, 4, '457406012030681088', '1', '1', 10, '签到发放', '2020-09-02 21:18:55', NULL, 0);
INSERT INTO `home_integral_log` VALUES (14, 4, '457406012030681088', '1', '1', 10, '签到发放', '2020-08-30 21:18:55', NULL, 0);

SET FOREIGN_KEY_CHECKS = 1;

sql语句

例如当前日期是2023-04-21，在数据库里再插入两条数据日期分别是：2023-04-19 21:18:55、2023-04-20 21:18:55

SELECT
    count( 1 )  as days
FROM
    (
    SELECT
        date_sub( a.create_time, INTERVAL 1 DAY ) signDate,
        ( @i := DATE_ADD( @i, INTERVAL - 1 DAY ) ) today 
    FROM
        ( SELECT create_time FROM home_integral_log WHERE m_id = 4 and integral_type=1 and del_flag=0   ORDER BY create_time DESC ) a
        INNER JOIN (
        SELECT
            @i := max( create_time ) AS signMax 
        FROM
            home_integral_log 
        WHERE
            m_id = 4 and integral_type=1 and del_flag=0
            AND (
                TO_DAYS( create_time ) = TO_DAYS(
                curdate()) 
                OR TO_DAYS( create_time ) = TO_DAYS( DATE_ADD( curdate(), INTERVAL - 1 DAY ) ) 
            ) 
        ) b 
    WHERE
        b.signMax IS NOT NULL 
        AND TO_DAYS(
        DATE_ADD( @i, INTERVAL - 1 DAY )) = TO_DAYS( date_sub( a.create_time, INTERVAL 1 DAY ) ) 
    ) c

查询结果是:days=2

如果是时间戳需要转换

SELECT
    count( 1 )  as days
FROM
    (
    SELECT
        date_sub( FROM_UNIXTIME(time,'%Y-%m-%d'), INTERVAL 1 DAY ) signDate,
        ( @i := DATE_ADD( @i, INTERVAL - 1 DAY ) ) today 
    FROM
        ( SELECT time FROM app_money WHERE uid = 57670 and type='sign'   ORDER BY time DESC ) a
        INNER JOIN (
        SELECT
            @i := max( FROM_UNIXTIME(time,'%Y-%m-%d') ) AS signMax 
        FROM
            app_money 
        WHERE
            uid = 57670 and type='sign'
            AND (
                TO_DAYS( FROM_UNIXTIME(time,'%Y-%m-%d') ) = TO_DAYS(
                curdate()) 
                OR TO_DAYS( FROM_UNIXTIME(time,'%Y-%m-%d') ) = TO_DAYS( DATE_ADD( curdate(), INTERVAL - 1 DAY ) ) 
            ) 
        ) b 
    WHERE
        b.signMax IS NOT NULL 
        AND TO_DAYS(
        DATE_ADD( @i, INTERVAL - 1 DAY )) = TO_DAYS( date_sub( FROM_UNIXTIME(time,'%Y-%m-%d'), INTERVAL 1 DAY ) ) 
    ) c