最近多次看到用SQL查询连续打卡信息问题,自己也实践一波。抛开问题本身,也是对MySQL窗口函数和自定义变量用法的一种练习。
01 建表 所用数据库为MySQL8.0,简单而不失一般性,建立一个仅有记录id、用户id、日期和打卡标记共4个字段的数据表。建表语句为:
1CREATE TABLE `testd` (
2 `id` int NOT NULL AUTO_INCREMENT,
3 `userid` int NOT NULL,
4 `dday` date DEFAULT(CURRENT_DATE),
5 `flag` tinyint(1) DEFAULT(0),
6 PRIMARY KEY (`id`)
7) ENGINE=InnoDB
为使查询更具一般性,设计数据表中有两个用户、日期存在跨月、且可能存在日期不连续的情形(虽然实际中可能并不存在这样的情况),插入如下数据:
1INSERT INTO `testd`(`id`, `userid`, `dday`, `flag`) VALUES (1, 1, '2020-03-31', 1);
2INSERT INTO `testd`(`id`, `userid`, `dday`, `flag`) VALUES (2, 1, '2020-04-01', 0);
3INSERT INTO `testd`(`id`, `userid`, `dday`, `flag`) VALUES (3, 1, '2020-04-02', 1);
4INSERT INTO `testd`(`id`, `userid`, `dday`, `flag`) VALUES (4, 1, '2020-04-03', 1);
5INSERT INTO `testd`(`id`, `userid`, `dday`, `flag`) VALUES (5, 1, '2020-04-04', 1);
6INSERT INTO `testd`(`id`, `userid`, `dday`, `flag`) VALUES (6, 1, '2020-04-05', 1);
7INSERT INTO `testd`(`id`, `userid`, `dday`, `flag`) VALUES (7, 1, '2020-04-07', 0);
8INSERT INTO `testd`(`id`, `userid`, `dday`, `flag`) VALUES (8, 1, '2020-04-08', 1);
9INSERT INTO `testd`(`id`, `userid`, `dday`, `flag`) VALUES (9, 1, '2020-04-09', 1);
10INSERT INTO `testd`(`id`, `userid`, `dday`, `flag`) VALUES (10, 1, '2020-04-10', 1);
11INSERT INTO `testd`(`id`, `userid`, `dday`, `flag`) VALUES (11, 1, '2020-04-11', 0);
12INSERT INTO `testd`(`id`, `userid`, `dday`, `flag`) VALUES (12, 2, '2020