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 );
推荐阅读