用SQL语句,删除掉重复项只保留一条

温馨提示:本文最后更新于2025-06-12 16:21:02,某些文章具有时效性,若有错误或已失效,请在下方留言!

用SQL语句,删除掉重复项只保留一条

在几千条记录里,存在着些相同的记录,如何能用SQL语句,删除掉重复的呢

1、查找表中多余的重复记录,重复记录是根据单个字段(id)来判断

//表中不是能过ID进行自增,id是城市的编号 //
SELECT  FROM Community WHERE id IN ( SELECT id FROM Community GROUP BY id HAVING count(id)> 1 ) order by id ;
// 通过id进行排序 *//

2、删除表中多余的重复记录,重复记录是根据单个字段(Comm_Name)来判断,只留有rowid最小的记录

 1093 - You can't specify target table 'Community' for update in FROM clause

表中字段Comm_ID 为自增ID

YDELETE * FROM Community WHERE Comm_Name IN ( SELECT Comm_Name FROM Community GROUP BY Comm_Name HAVING count(Comm_Name )>1) AND Comm_ID NOT IN ( SELECT min(Comm_ID) FROM Community GROUP BY Comm_Name HAVING count ( Comm_Name )>1 );

3、查找表中多余的重复记录(多个字段)

SELECT  FROM Community c WHERE (c.id, c.name) IN (SELECT id, name FROM Community GROUP BY id, name HAVING count()>1)

4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录

DELETE FROM Community c WHERE (c.id, c.name) IN (SELECT id, name FROM Community GROUP BY id, name HAVING count()>1) AND rowid NOT IN ( SELECT min(rowid) FROM Community GROUP BY id, name HAVING count()>1 )

5、查找表中多余的重复记录(多个字段),不包含rowid最小的记录

SELECT FROM Community c WHERE (c.id,c.name) IN (SELECT id,name FROM Community GROUP BY id,name HAVING count()>1) AND rowid NOT IN (SELECT min(rowid) FROM Community GROUP BY id,name HAVING count(*) > 1);

本站资源均为网友推荐收集整理而来,请勿商业运营,仅供学习和研究,请在下载后24小时内删除!!
© 版权声明
THE END
喜欢就支持一下吧
点赞14 分享
评论 抢沙发

请登录后发表评论

    暂无评论内容