mysql case when 去重_MYSQL去重----有id版本OK，无id下一步考虑完全删除的情况

MYSQL去重----有id版本OK，无id下一步考虑完全删除的情况

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

INOUT p_Del_Num INT #本次删除的数量

)

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='';

# 有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)

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));

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.',@p_Auto_Column,' is null ;'

);

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.',@p_Auto_Column,' is null ;'

);

END IF;

#PREPARE stmt FROM @p_sql;

#EXECUTE stmt;

SELECT @p_sql;

END;

&&

DELIMITER ;

CALL P_DeRep_data('test','bo_id','license_plate,source,run_status','min,max,max',@p_Del_Num);

2019-11-20 11:12