mysql统计某列值连续出现次数小于五次的记录

来源：互联网发布：旅游网站系统思途cms 编辑：程序博客网时间：2024/05/01 13:09

表原纪录如下：

需求：找出age连续出现次数少于5次的所有记录。

#建临时表

CREATE TABLE dba.t3_temp AS SELECT cc.age,rownum,cc.orinum,cc.id,cc.nameFROM (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;

效果如下：

--不当之处，欢迎指正。

0 0