统计连续三个月以上的mysql

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for records
-- ----------------------------
DROP TABLE IF EXISTS `records`;
CREATE TABLE `records`  (
  `id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `thing` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `date` datetime(0) NOT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of records
-- ----------------------------
INSERT INTO `records` VALUES ('1', 'test01', 'thing01', '2014-01-02 00:00:00');
INSERT INTO `records` VALUES ('2', 'test01', 'thing02', '2014-01-02 00:00:00');
INSERT INTO `records` VALUES ('3', 'test01', 'thing02', '2014-02-27 00:00:00');
INSERT INTO `records` VALUES ('4', 'test01', 'thing02', '2014-04-27 00:00:00');
INSERT INTO `records` VALUES ('5', 'test02', 'thing02', '2014-01-28 00:00:00');
INSERT INTO `records` VALUES ('6', 'test02', 'thing01', '2014-02-28 00:00:00');
INSERT INTO `records` VALUES ('7', 'test02', 'thing02', '2014-03-28 00:00:00');
INSERT INTO `records` VALUES ('8', 'test02', 'thing02', '2014-05-29 00:00:00');
INSERT INTO `records` VALUES ('9', 'test02', 'thing02', '2014-06-29 00:00:00');

SET FOREIGN_KEY_CHECKS = 1;

思路参照 起一个临时变量做为排序值 通过日期月份相减得出一个同值日期 进行分组就能得出连续的次数数据
https://cloud.tencent.com/developer/article/1559387

SET @row_number = 1;
SELECT name,count(1) as count FROM
(
SELECT
	( @row_number := @row_number + 1 ) AS ROW_NUMBER ,
	name,date
from records ORDER BY name,date
)t GROUP BY name,  DATE_FORMAT(SUBDATE(date ,INTERVAL  row_number MONTH),'%Y%m') HAVING count(1) >2

结果:

name	count
test02	3