oracle 去除表中重复记录

[zhaoyang0405]

好

去除表中重复记录，根据多个字段，不是所有字段，怎么办？

[zhaoyang0405]

我想的是不让他显示，类似与distinct

[zcs_1]

如下,表TAB01
create table tab01(a int, b int, c int);
INSERT INTO tab01 values(1,1,0);
INSERT INTO tab01 values(1,1,1);
INSERT INTO tab01 values(1,1,2);
INSERT INTO tab01 values(1,2,0);
INSERT INTO tab01 values(1,2,1);
INSERT INTO tab01 values(1,2,2);
commit;

a b c
1 1 0
1 1 1
1 1 2
1 2 0
1 2 1
1 2 2

去掉后最后是
a b c
1 1 0
1 2 0


SQL为：
DELETE FROM tab01
WHERE rowid IN
(SELECT rowid FROM
(
SELECT rowid, a, b, c, row_number() over(partition by a, b order by rowid) rn FROM tab01
)
WHERE rn > 1
);

[fuyou001]

[Quote=引用 1 楼 hdhai9451 的回复:]
select *
from
(select t.*,row_number() orver(partition by col1,col2,col3...) rn from tb)
where rn>1

其中col1,col2,col3...为重复的字段
[/Quote]
顶这个

[fuyou001]

select *
from
(select t.*,row_number() orver(partition by col1,col2,col3...) rn from tb)
where rn=1 --只取一条

其中col1,col2,col3...为重复的字段

[Andy__Huang]

select *
from
(select t.*,row_number() orver(partition by col1,col2,col3...) rn from tb)
where rn>1

其中col1,col2,col3...为重复的字段

[Andy__Huang]

上面是查找出了重复的记录
然后删除,假设id为主键

delete tb
where id in(
select id
from
(select t.*,row_number() orver(partition by col1,col2,col3...) rn from tb)
where rn>1 );
commit;