欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

mysql清除重复数据,只保留一条最新记录

程序员文章站 2022-06-28 09:27:34
...

1,查出重复数据,现以card_number 重复就认定为重复

SELECT card_number FROM crm_def_customer GROUP BY card_number HAVING count(card_number) > 1)

 

2,查出最新的数据

SELECT max(create_date) FROM crm_def_customer GROUP BY card_number HAVING count(card_number) > 1

 

3,组合查出重复数据排除最新记录在外

 SELECT id from (
        SELECT id,card_number FROM crm_def_customer WHERE card_number in(
            SELECT card_number FROM crm_def_customer GROUP BY card_number HAVING count(card_number) > 1)
            AND create_date  not IN(SELECT min(create_date) FROM crm_def_customer GROUP BY card_number HAVING count(card_number) > 1)) as t 

//删除前也可查出name等更多项确保sql语句正确

4,作删除

DELETE  from   crm_def_customer where id in(
 SELECT id from (
        SELECT id,card_number FROM crm_def_customer WHERE card_number in(
            SELECT card_number FROM crm_def_customer GROUP BY card_number HAVING count(card_number) > 1)
            AND create_date  not IN(SELECT min(create_date) FROM crm_def_customer GROUP BY card_number HAVING count(card_number) > 1)
 ) as t );

 

相关标签: mysql数据库