案例：
最近遇到一个业务需求， 需要查找满足条件且连续3出现条以上的记录。
表结构：CREATE TABLE `cdb_labels` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`type` int(11) NOT NULL DEFAULT '0' COMMENT '标签类型：1喜欢异性类型,2擅长话题',`content` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '标签内容',PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=57 DEFAULT CHARSET=utf8 COMMENT='标签内容';
所有数据： SELECT * FROM cdb_labels WHERE type = 1;
解决思路：
1. 对满足初次查询的数据赋予一个自增列b ：
select id,type,content,(@b:=@b+1) as b from cdb_labels a,(SELECT @b := 0) tmp_b where type=1;
2、用自增的id减去自增列b:
select id,type,content,( id-(@b:=@b+1) ) as c from cdb_labels a,(SELECT @b := 0) tmp_b where type=1;
3、对等差列c分组, 并将分组的id组装起来:
select count(id),GROUP_CONCAT(id) from ( select id,( id-(@b:=@b+1) ) as c from cdb_labels a,(SELECT @b := 0) tmp_b where type=1 ) as d GROUP BY c;
注：为了方便区分，这里查询分组成员要大于5（也就是连续出现超过5次的记录）:select if( count(id)>5 ,GROUP_CONCAT(id),null) e from ( select id,( id-(@b:=@b+1) ) as c from cdb_labels a,(SELECT @b := 0) tmp_b where type=1 ) as d GROUP BY c;那么得到的数据只有：9,10,11,12,13,14,15
4、根据组装的id去找数据：
select id,type,content from cdb_labels,(select if( count(id)>5 ,GROUP_CONCAT(id),null) e from ( select id,( id-(@b:=@b+1) ) as c from cdb_labels a,(SELECT @b := 0) tmp_b where type=1 ) as d GROUP BY c) as f where f.e is not null and FIND_IN_SET(id , f.e);
