希望长大对我而言，是可以做更多想做的事，而不是被迫做更多不想做的事...... 首页

MYSQL-连续登陆的天数

丁D
学无止境
2020-05-27 11:09
36121已阅读

sql

摘要求连续登陆的天数或者签到数

数据

![](/upload/F1E48BEB-FD54-42e2-BFC1-5A067F637860.png)

求连续登陆的天数

```
CREATE TABLE `t_login` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `login_date` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

INSERT INTO `blog`.`t_login` (`id`, `name`, `login_date`) VALUES ('1', '丁D', '2020-05-27');
INSERT INTO `blog`.`t_login` (`id`, `name`, `login_date`) VALUES ('2', '丁D', '2020-05-26');
INSERT INTO `blog`.`t_login` (`id`, `name`, `login_date`) VALUES ('3', '丁D', '2020-05-25');
INSERT INTO `blog`.`t_login` (`id`, `name`, `login_date`) VALUES ('4', '丁D', '2020-05-15');
INSERT INTO `blog`.`t_login` (`id`, `name`, `login_date`) VALUES ('5', '丁D', '2020-05-27');
INSERT INTO `blog`.`t_login` (`id`, `name`, `login_date`) VALUES ('6', '丁D', '2020-05-16');
INSERT INTO `blog`.`t_login` (`id`, `name`, `login_date`) VALUES ('7', '丁D', '2020-05-26');
INSERT INTO `blog`.`t_login` (`id`, `name`, `login_date`) VALUES ('8', '丁D1', '2020-05-26');
INSERT INTO `blog`.`t_login` (`id`, `name`, `login_date`) VALUES ('9', '丁D1', '2020-05-26');
INSERT INTO `blog`.`t_login` (`id`, `name`, `login_date`) VALUES ('10', '丁D2', '2020-05-27');

SELECT
			t1. NAME ,@cont_day := (
				CASE
				WHEN @last_uid = t1. NAME
				AND DATEDIFF(t1.login_date, @last_ot) = 1 THEN
					(@cont_day + 1)
				WHEN @last_uid = t1. NAME
				AND DATEDIFF(t1.login_date, @last_ot) < 1 THEN
					(@cont_day + 0)
				ELSE
					1
				END
			) AS days,
			@last_uid := t1. NAME,
			@last_ot := t1.login_date,
			t1.login_date
		FROM
			(
				SELECT
					*
				FROM
					t_login
				ORDER BY
					NAME,
					login_date
			) t1,
			(
				SELECT
					@last_uid := '',
					@last_ot := '',
					@cont_day := 0
			) AS t2
```
![](/upload/B467752B-0740-4446-9011-248F4EA806E5.png)

上一篇：Elasticsearch索引管理

下一篇：tesseract-ocr验证码识别