mysql变量查询实现计数排行

先看mysql一个简单的变量查询：

SET @rowNum = 0; 
SELECT ( @rowNum := @rowNum + 1 ) AS rankNum;

变量初始值rowNum设置为0，查询一行数据变量+1，定义为排行号，也是数据行号

用途一：数据排行

现在签到表结构如下：

CREATE TABLE `sign_in` (
  `phone` varchar(11) NOT NULL COMMENT '登录手机号',
  `integral_num` decimal(3,0) NOT NULL DEFAULT '0' COMMENT '签到获取的积分',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '签到时间',
  KEY `sign_in_phone` (`phone`) USING BTREE,
  KEY `sign_in_create_time` (`create_time`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='签到表';

1、要查询累计签到天数排行前10，查询sql如下：

SET @rowNum = 0;
SELECT
	( @rowNum := @rowNum + 1 ) AS rowNo,
	t.phone,
	t.count 
FROM
	( SELECT phone, count( 1 ) AS count FROM sign_in  GROUP BY phone ORDER BY count DESC LIMIT 10 ) t;

2、查询指定的人累计签到数排行，在上面的基础上加上一个where phone = xxx条件：

SET @rowNum = 0;
SELECT
	tt.rowNo,
	tt.phone,
	tt.count 
FROM
	(
	SELECT
		( @rowNum := @rowNum + 1 ) AS rowNo,
		t.phone,
		t.count 
	FROM
		( SELECT phone, count( 1 ) AS count FROM sign_in GROUP BY phone ORDER BY count DESC ) t 
	) tt 
WHERE
	tt.phone = 'xxx';

上面的sql用到了两个sql执行，用在mybatis项目中执行会报错，解决办法看下一篇：项目中SET @rowNum = 0和SELECT语句一起执行

用途二：计数统计

还是上面的签到表，统计一个人连续签到天数

SELECT
	count( 1 ) 
FROM
	(
	SELECT
		DATE_SUB( a.create_time, INTERVAL 1 DAY ) signDate,
		( @i := DATE_SUB( @i, INTERVAL 1 DAY ) ) today 
	FROM
		( SELECT create_time FROM sign_in WHERE phone = '33' ORDER BY create_time DESC ) a
		INNER JOIN (
		SELECT
			@i := max( create_time ) AS signMax 
		FROM
			sign_in 
		WHERE
			phone = '33' 
			AND (
				TO_DAYS( create_time ) = TO_DAYS( curdate( ) ) 
				OR TO_DAYS( create_time ) = TO_DAYS( DATE_SUB( curdate( ), INTERVAL 1 DAY ) ) 
			) 
		) b 
	WHERE
		b.signMax IS NOT NULL 
		AND TO_DAYS(@i) = TO_DAYS(a.create_time) 
	) c

分析一下查询逻辑：

1、第一句要从这里说起 ，查询手机号33的人的签到时间倒叙集合

SELECT create_time FROM sign_in WHERE phone = '33' ORDER BY create_time DESC 

2、第二部分就是联合变量，查询的最后一次签到时间

SELECT
            @i := max( create_time ) AS signMax 
        FROM
            sign_in 
        WHERE
            phone = '33' 
            AND (
                TO_DAYS( create_time ) = TO_DAYS( curdate( ) ) 
                OR TO_DAYS( create_time ) = TO_DAYS( DATE_SUB( curdate( ), INTERVAL 1 DAY ) ) 
            ) 

 条件是签到时间是今天或者昨天的时间，选出一个最近的，也是最大的，这个结果有三种：今天，昨天，或者空

3、然后前两部分内容联合之后，查询两个日期，一个签到时间，第二个是变量记录的时间

SELECT
        DATE_SUB( a.create_time, INTERVAL 1 DAY ) signDate,
        ( @i := DATE_SUB( @i, INTERVAL 1 DAY ) ) today 
    FROM

 这两个日期时间都减去了一天，这是为了要记录日期的变量“变化”，要让变量随着每一行数据一天一天变化，可以保证这个变量日期是连续的。

第一个签到日期也减一天，这是为了匹配当天签到时候和变量保持一致，不然会丢一天

4、最后一部分就是where条件里的限制

b.signMax IS NOT NULL 
        AND TO_DAYS(@i) = TO_DAYS(a.create_time) 

上面2部分说的可能是空，空标识断签了，不管从哪天断的查出来都是0，所以第一个条件就是这个参数不为空

第二个条件就是两个日期时间要相等，参照的就是变量日期是一天一天减下来的，和它相等的才是连续签到的，只要不连续的都会往前排导致日期不匹配被过滤掉。