本文共 1932 字,大约阅读时间需要 6 分钟。
-- 构造测试环境
SQL> create table andy(id int,name varchar2(10)); Table created. SQL> insert into andy values(1,'a'); insert into andy values(2,'b'); insert into andy values(3,'c'); insert into andy values(4,'d'); SQL> select * from andy;ID NAME
---------- ---------- 1 a 2 b 3 c 4 d 4 rows selected. SQL> insert into andy values(4,'f'); insert into andy values(4,'d');SQL> select * from andy;
ID NAME ---------- ---------- 1 a 2 b 3 c 4 d 4 f 4 d 6 rows selected. -- 依次group by 表所有字段,通过min(rowid)查看所有唯一记录(去重记录,也就是相同多行数据只显示一行) SQL> select id,name,min(rowid) from andy group by id,name; ID NAME MIN(ROWID) ---------- ---------- ------------------ 3 c AAAfKTAAEAAACr/AAC 4 d AAAfKTAAEAAACr/AAD 4 f AAAfKTAAEAAACr/AAJ 1 a AAAfKTAAEAAACr/AAA 2 b AAAfKTAAEAAACr/AAB -- delete 重复数据时,group by 表的个别字段,发现误删除 SQL> delete from andy where rowid not in ( select min(rowid) from andy group by id); 2 rows deleted. 说明:记录 4 f 被误删。 SQL> select * from andy;ID NAME
---------- ---------- 1 a 2 b 3 c 4 d -- 构造与上面测试相同环境,即插入刚删除的数据 SQL> insert into andy values(4,'f'); insert into andy values(4,'d'); SQL> select * from andy; ID NAME ---------- ---------- 1 a 2 b 3 c 4 d 4 f 4 d 6 rows selected. -- 依次group by 表所有字段,通过min(rowid)查看所有唯一记录(去重记录,也就是相同多行数据只显示一行) SQL> select id,name,min(rowid) from andy group by id,name; ID NAME MIN(ROWID) ---------- ---------- ------------------ 3 c AAAfKTAAEAAACr/AAC 4 d AAAfKTAAEAAACr/AAD 4 f AAAfKTAAEAAACr/AAL 1 a AAAfKTAAEAAACr/AAA 2 b AAAfKTAAEAAACr/AAB -- delete 重复数据时,group by 表的所有字段,发现没有误删。 SQL> delete from andy where rowid not in ( select min(rowid) from andy group by id,name); 1 row deleted. -- 检查去重后的数据,发现没有误删 SQL> select * from andy; ID NAME ---------- ---------- 1 a 2 b 3 c 4 d 4 f 说明: 如果想通过rowid去重,那么在 delete 重复数据时,需要group by 表的所有字段。如果只group by 表的个别字段,那么会造成误删除。来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31383567/viewspace-2139824/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31383567/viewspace-2139824/