目录
用SQL语句,删除掉重复项只保留一条
在几千条记录里,存在着些相同的记录,如何能用SQL语句,删除掉重复的呢
1、查找表中多余的重复记录,重复记录是根据单个字段(id)来判断
1 2 3 |
/<em> 表中不是能过ID进行自增,id是城市的编号 </em>/ SELECT <em> FROM Community WHERE id IN ( SELECT id FROM Community GROUP BY id HAVING count(id)> 1 ) order by id ; /</em> 通过id进行排序 */ |
1 2 3 |
/<em> 表中不是能过ID进行自增,id是城市的编号 </em>/ SELECT <em> FROM Community WHERE id IN(SELECT id FROM Community GROUP BY id HAVING count(id)> 1) order by id ; /</em> 通过id进行排序 */ |
2、删除表中多余的重复记录,重复记录是根据单个字段(Comm_Name)来判断,只留有rowid最小的记录
MYSQL中无法执行这段代码,请使用下面的代码
1 |
1093 - You can't specify target table 'Community' for update in FROM clause |
1 2 3 |
</p> <h1>表中字段Comm_ID 为自增ID</h1> <p>DELETE * 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 ); |
MYSQL数据库的操作
3、查找表中多余的重复记录(多个字段)
1 |
SELECT <em> FROM Community c WHERE (c.id, c.name) IN (SELECT id, name FROM Community GROUP BY id, name HAVING count(</em>)>1) |
4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录
1 |
DELETE FROM Community c WHERE (c.id, c.name) IN (SELECT id, name FROM Community GROUP BY id, name HAVING count(<em>)>1) AND rowid NOT IN ( SELECT min(rowid) FROM Community GROUP BY id, name HAVING count(</em>)>1 ) |
5、查找表中多余的重复记录(多个字段),不包含rowid最小的记录
1 |
SELECT <em> FROM Community c WHERE (c.id,c.name) IN (SELECT id,name FROM Community GROUP BY id,name HAVING count(</em>)>1) AND rowid NOT IN (SELECT min(rowid) FROM Community GROUP BY id,name HAVING count(*) > 1); |
评论(0)