mysql删除多处字段重复的数据

1、拼接字段查出重复的数据

SELECT id,concat( spare_id,user_id,stock_id) uuid FROM t_spare_user_rel WHERE stock_id is not null;

2、以新字段为条件查出重复数据

SELECT COUNT(a.id),a.uuid FROM (SELECT id,concat( spare_id,user_id,stock_id) uuid FROM t_spare_user_rel WHERE stock_id is not null
) a GROUP BY a.uuid HAVING COUNT(a.id) > 1

3、查出最小的一条数据

SELECT min(a.id) FROM (SELECT id,concat( spare_id,user_id,stock_id) uuid FROM t_spare_user_rel WHERE stock_id is not null
) a GROUP BY a.uuid HAVING COUNT(a.id) > 1

4、删除最小的重复数据

DELETE FROM t_spare_user_rel WHERE id IN(SELECT min(a.id) FROM (SELECT id,concat( spare_id,user_id,stock_id) uuid FROM t_spare_user_rel WHERE stock_id is not null) a GROUP BY a.uuid HAVING COUNT(a.id) > 1
)

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！