oracle如何消除重复,oracle如何去除重复数据

oracle去除重复数据的方法：1、针对指定列，查出所有重复的行，并删除，方法为count having；2、删除所有重复的行，代码为【delete from nayi224_180824 t where t.rowid in】。

本文操作环境：Windows7系统，oracle9i版本，Dell G3电脑。

oracle去除重复数据的方法：

创建测试数据create table nayi224_180824(col_1 varchar2(10), col_2 varchar2(10), col_3 varchar2(10));

insert into nayi224_180824select 1, 2, 3 from dual union allselect 1, 2, 3 from dual union allselect 5, 2, 3 from dual union allselect 10, 20, 30 from dual ;commit;select*from nayi224_180824;COL_1COL_2COL_3123

123

523

102030

针对指定列，查出去重后的结果集

distinctselect distinct t1.* from nayi224_180824 t1;COL_1COL_2COL_3102030

123

523

方法局限性很大，因为它只能对全部查询的列做去重。如果我想对col_2,col3去重，那我的结果集中就只能有col_2,col_3列，而不能有col_1列。select distinct t1.col_2, col_3 from nayi224_180824 t1COL_2COL_323

2030

不过它也是最简单易懂的写法。

row_number()select * from (select t1.*,

row_number() over(partition by t1.col_2, t1.col_3 order by 1) rn

from nayi224_180824 t1) t1 where t1.rn = 1;COL_1COL_2COL_3RN1231

1020301

写法上要麻烦不少，但是有更大的灵活性。

针对指定列，查出所有重复的行

count havingselect * from nayi224_180824 t

where (t.col_2, t.col_3) in (select t1.col_2, t1.col_3

from nayi224_180824 t1

group by t1.col_2, t1.col_3

having count(1) > 1)COL_1COL_2COL_3123

123

523

要查两次表，效率会比较低。不推荐。

count overselect * from (select t1.*,

count(1) over(partition by t1.col_2, t1.col_3) rn

from nayi224_180824 t1) t1 where t1.rn > 1;COL_1COL_2COL_3RN1233

1233

5233

只需要查一次表，推荐。

删除所有重复的行delete from nayi224_180824 t where t.rowid in (

select rid

from (select t1.rowid rid,

count(1) over(partition by t1.col_2, t1.col_3) rn

from nayi224_180824 t1) t1

where t1.rn > 1);

就是上面的语句稍作修改。

删除重复数据并保留一条

分析函数法delete from nayi224_180824 t where t.rowid in (select rid

from (select t1.rowid rid,

row_number() over(partition by t1.col_2, t1.col_3 order by 1) rn

from nayi224_180824 t1) t1

where t1.rn > 1);

拥有分析函数一贯的灵活性高的特点。可以为所欲为的分组，并通过改变orderby从句来达到像”保留最大id“这样的要求。

group bydelete from nayi224_180824 t where t.rowid not in

(select max(rowid) from nayi224_180824 t1 group by t1.col_2, t1.col_3);

牺牲了一部分灵活性，换来了更高的效率。