mysql count if 去重_MYSQL去重最终版

DELIMITER ; DROP PROCEDURE IF EXISTS P_DeRep_data ; DELIMITER && CREATE PROCEDURE P_DeRep_data  ( IN p_Rep_table VARCHAR(50), IN p_Key_Columns VARCHAR(255), #唯一字段列表 IN p_Rep_Columns VARCHAR(255), #重复字段列表 --未列出的字段代表不需要关系。1-->N。前面为主要字段 IN p_Rep_Columns_Keep VARCHAR(255) #重复字段保留值，min max ) top:BEGIN #定义存储过程顶部以方便跳出存储过程  # declare p_condition varchar(255) Default '';  默认值的定义方法 # 创建表 DECLARE p_Auto_Column VARCHAR(50); DECLARE var_sql VARCHAR(4000); IF  NOT EXISTS (SELECT 1 FROM information_schema.`TABLES`  WHERE table_schema=DATABASE() AND table_name =p_Rep_table) THEN  SELECT '数据表不存在'; LEAVE top; #强制跳出 END IF ; SELECT @p_Auto_Column:=column_name FROM information_schema.`columns`   WHERE table_schema=DATABASE() AND Extra='auto_increment' AND table_name =p_Rep_table; SET @Group_List=''; SET @Group2_List=''; SET @Agg_List=''; SET @Join_List=''; SET @Where_List=''; SET @Full_Delete=''; # 有id的 SELECT   @Group_List:=CONCAT(@Group_List,',',COLUMN_NAME) #有无id都一样 ,@Group2_List:=CONCAT(@Group2_List,',B.',COLUMN_NAME) #有无id都一样 ,@Join_List:=CONCAT(@Join_List,' and A.',COLUMN_NAME,'=B.',COLUMN_NAME) ,@Right_Null:=NULLIF(@p_Auto_Column,COLUMN_NAME) ,@Full_Delete:=CONCAT(@Full_Delete,' and ',COLUMN_NAME,'='',',COLUMN_NAME,',''') FROM  ( SELECT a.`COLUMN_NAME` ,FIND_IN_SET(a.`COLUMN_NAME`, p_Key_Columns) AS iOrder  FROM information_schema.`COLUMNS` a WHERE table_schema=DATABASE() AND table_name=p_Rep_table ORDER BY iOrder ) AS a WHERE a.iOrder>=1 ; SELECT   @Agg_List:=CONCAT(@Agg_List,',',(SUBSTRING(p_Rep_Columns_Keep,(iOrder-1)*4+1,3)),'(',COLUMN_NAME,') as ',COLUMN_NAME)#有无id都一样 ,@Join_List:=(CASE WHEN iOrder=1 THEN CONCAT(@Join_List,' and A.',COLUMN_NAME,'=B.',COLUMN_NAME) ELSE @Join_List END) ,@Where_List:=(CASE WHEN iOrder>1 THEN CONCAT(@Where_List,' and A.',COLUMN_NAME,'=B.',COLUMN_NAME) ELSE @Where_List END) FROM  ( SELECT a.`COLUMN_NAME` ,FIND_IN_SET(a.`COLUMN_NAME`, p_Rep_Columns) AS iOrder  FROM information_schema.`COLUMNS` a WHERE table_schema=DATABASE() AND table_name=p_Rep_table ORDER BY iOrder ) AS a WHERE a.iOrder>=1 ; SET @Group_List=RIGHT(@Group_List,(CHAR_LENGTH(@Group_List)-1)); SET @Group2_List=RIGHT(@Group2_List,(CHAR_LENGTH(@Group2_List)-1)); SET @Join_List=RIGHT(@Join_List,(CHAR_LENGTH(@Join_List)-4)); SET @Full_Delete=RIGHT(@Full_Delete,(CHAR_LENGTH(@Full_Delete)-4)); IF @p_Auto_Column IS  NOT NULL THEN  SET @Where_List=CONCAT(' where ',RIGHT(@Where_List,(CHAR_LENGTH(@Where_List)-4))); SET @p_sql=CONCAT(' delete B2 from  (select Max(B.',@p_Auto_Column,') as ',@p_Auto_Column,' from  (select ',@Group_List,@Agg_List,' ','  from ',p_Rep_table,' group by ',@Group_List,') as A right join ',p_Rep_table,' as B on ',@Join_List,' ',@Where_List,' group by ',@Group2_List,') as A2 right join ',p_Rep_table,' as B2 on A2.',@p_Auto_Column,'=B2.',@p_Auto_Column,' where A2.',@Right_Null,' is null ;' ); PREPARE stmt FROM @p_sql; EXECUTE stmt;  ELSE  #基础去重。非完全重复的去重 SET @p_sql=CONCAT(' delete B from  (select ',@Group_List,@Agg_List,' ','  from ',p_Rep_table,' group by ',@Group_List,') as A right join ',p_Rep_table,' as B on ',@Join_List,' ',@Where_List,' where A.',@Right_Null,' is null ;' ); PREPARE stmt FROM @p_sql; EXECUTE stmt;  # select @p_sql; # 可删除全部重复数据的存储过程体 SET @p_sql2=CONCAT(' DECLARE var_sql VARCHAR(1000); DECLARE var_Where VARCHAR(1000); DECLARE var_Limit_num VARCHAR(1000); DECLARE var_table VARCHAR(1000) DEFAULT ''',p_Rep_table,'''; DECLARE var_done INT DEFAULT FALSE; DECLARE cur_delete_rep_full CURSOR FOR SELECT CONCAT(''delete from ',p_Rep_table,' where ',@Full_Delete,' limit '',COUNT(1)-1,'';'') FROM ',p_Rep_table,' GROUP BY ',@Group_List,' HAVING COUNT(1)>1;   DECLARE CONTINUE HANDLER FOR NOT FOUND SET var_done = TRUE; IF EXISTS (SELECT STATUS,bo_id  FROM ',p_Rep_table,' GROUP BY ',@Group_List,' HAVING COUNT(1)>1) THEN  OPEN cur_delete_rep_full;  read_loop:LOOP    FETCH cur_delete_rep_full INTO var_sql;    IF var_done THEN      LEAVE read_loop;    END IF;    SET @p_sql=var_sql;    PREPARE stmt FROM @p_sql;    EXECUTE stmt;     END LOOP;  CLOSE cur_delete_rep_full; END IF ; '); # 使用临时表保存数据去重条件 DROP TEMPORARY TABLE IF EXISTS full_dep_condition; CREATE TEMPORARY TABLE full_dep_condition(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,cSql VARCHAR(1000)); SET @p_sql3=CONCAT(' insert into full_dep_condition(cSql) SELECT CONCAT(''delete from ',p_Rep_table,' where ',@Full_Delete,' limit '',COUNT(1)-1,'';'') FROM ',p_Rep_table,' GROUP BY ',@Group_List,' HAVING COUNT(1)>1;'); PREPARE stmt FROM @p_sql3; EXECUTE stmt;  SELECT @m:=MIN(id),@n:=MAX(id) FROM full_dep_condition; WHILE @m<=@n DO  SET @p_sql4=(SELECT cSQL FROM full_dep_condition WHERE id=@m); PREPARE stmt FROM @p_sql4; EXECUTE stmt;  SET @m=@m+1; END WHILE; END IF; END; && DELIMITER ;