rowid去重(删除表的重复记录)

-- 构造测试环境
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 表的个别字段,那么会造成误删除。

posted on 2017-05-24 20:49  张冲andy  阅读(2375)  评论(0编辑  收藏  举报

导航