mysql 统计连续重复值_mysql统计某列值连续出现次数小于五次的记录
表原纪录如下：
需求：找出age连续出现次数少于5次的所有记录。
#建临时表
CREATE TABLE dba.t3_temp AS
SELECT
cc.age,rownum,cc.orinum,cc.id,cc.name
FROM
(SELECT
aa.id,
aa.age,
aa.name,
aa.orinum,
IF(
(aa.age = @page),
@row := @row + 1,
@row := 1
)rownum,
@page := aa.age pn
FROM (
SELECT id,age,NAME,(@rowNum:=@rowNum+1) AS orinum FROM
dba.t3 ib,
(SELECT
@row := 0,
@page := '000000') row1,
(SELECT (@rowNum :=0) ) bb )aa) cc
用途：将连续出现的age对应的rownum递增，遇到新值时，rownum赋值为1.
orinum模拟的是行号。
该临时表查询效果如下：
--建个新表
CREATE TABLE dba.t3_new LIKE dba.t3;
ALTER TABLE dba.`t3_new` ADD group_idVARCHAR(100);
--存储过程
实现原理：
查询临时表rownum为1的记录数v_cnt，分为v_cnt个组。第一个rownum为1的行和第二个rownum为1的行之间(前闭后开)的记录则为第一个组.依次类推。最后一个组数据特殊些，需要额外处理一下。将group_id及相应的数据插入到新表中。
DELIMITER //
CREATE PROCEDURE p6()
BEGIN
DECLARE v_cnt INT DEFAULT 1;
DECLARE v_N INT DEFAULT 1;
DECLARE v_N2 INT;
DECLARE v_cnt2 INT;
SET v_N2 = v_N - 1;
SELECT COUNT(*) INTO v_cnt
FROM dba.`t3_temp`
WHERE rownum = 1 ;
SET v_cnt2 = v_cnt - 1;
#插入前N个rownum为1的所有数据
WHILE v_N < v_cnt DO
INSERT INTO dba.`t3_new`(group_id,age,NAME,id)
SELECT v_N,age,NAME,id FROM dba.`t3_temp`
WHERE orinum >=(
SELECT orinum FROM dba.`t3_temp`
WHERE rownum = 1
ORDER BY orinum
LIMIT v_N2,1)
AND orinum <
(
SELECT orinum FROM dba.`t3_temp`
WHERE rownum = 1
ORDER BY orinum
LIMIT v_N,1
);
SET v_N = v_N + 1;
SET v_N2 = v_N2 + 1;
END WHILE;
#插入最后一个rownum为1的数据
#select v_cnt;
#select v_cnt2;
INSERT INTO dba.`t3_new`(group_id,age,NAME,id)
SELECT v_cnt,age,NAME,id FROM dba.`t3_temp`
WHERE orinum >=(
SELECT orinum FROM dba.`t3_temp`
WHERE rownum = 1
ORDER BY orinum
LIMIT v_cnt2,1)
AND orinum <=
(
SELECT orinum FROM dba.`t3_temp`
ORDER BY orinum DESC
LIMIT 1
);
END ;
//
CALL p6();
--查询连续出现相同age小于5次的记录
SELECT age,COUNT(*)
FROM dba.`t3_new`
GROUP BY group_id,age
HAVING COUNT(*)<5;
效果如下：
--不当之处，欢迎指正。