delete from table where rowid in ( select rowid from (select c1,c2,c3.....,row_number() over(partition by c1,c2,c3..... order by rowid) rn from table) where rn>1)
delete from tt where rowid in(select rd from( select rowid rd,row_number()over(partition by 字段 order by rownum)rn from tt) where rn<>1)
delete from a where rowid not in (select max(rowid) from a group by xxx)这里xxx就是判断重复的字段比如你是id重复,就id如果是id,name重复,就id,name
那看你用的oracle版本才行,上面的就不能实现。
SQL> select name from ttt1;NAME ---------- 1111 1111 2222 2222SQL> delete from ttt1 where rowid not in (select max(rowid) from ttt1 group by n ame);SQL> select name from ttt1;NAME ---------- 1111 2222
create table tt(id int,a varchar2(10),b varchar2(10),c varchar2(10));insert into tt values(1,'a','b','c'); insert into tt values(1,'a','b','c'); insert into tt values(1,'a','b','c'); insert into tt values(1,'a','b','c'); insert into tt values(1,'a','b','c'); insert into tt values(2,'a1','b','c1'); insert into tt values(2,'a1','b','c1'); insert into tt values(3,'a1','b','c1'); insert into tt values(2,'a1','b','c1'); insert into tt values(3,'a1','b','c1'); insert into tt values(3,'a1','b','c1');delete from tt where exists (select rid from( select id,a,b,c,row_number() over(partition by id,a,b,c order by rowid) rn,rowid rid from tt) b where tt.rowid=b.rid and rn>1)commit;
delete * from table_name where table_name.id not in(select min(id) from table_name group by id,name.....)对表中的字段进行分组,然后取出id最小的,在删除时则删除id不是最小的 呵呵 和三楼的大同小异
3楼和11楼的这两个方法可以只不过一个保留的是最大的rowid,一个保留的是最小的id记录。 很多关于这样操作的资料上都提供了这样的两种方式。 保留最小 delete from a where rowid not in ( select min(rowid) from a group by XXX );
只能用sql语句实现,rowid是oracle中的
学习中:create table test (id number, name varchar2(40));insert into test(id,mame) values (1,'huangbiquan');insert into test(id,mame) values (1,'huangbiquan');insert into test(id,mame) values (1,'huangbiquan');insert into test(id,mame) values (2,'xiaoquan');insert into test(id,mame) values (3,'bishui');insert into test(id,mame) values (4,'youyouquan');commit;查询相同记录 1.select * from test t where t.rowid > (select min(x.rowid) from test x where t.id = x.id); 2.select * from test t where t.rowid <> (select max(x.rowid) from test x where t.id = x.id); 3.select count(*),t.id, t.name from test t group by id,name having count(*) > 1;查询不相同的记录 1.select * from test t where t.rowid <= (select min(x.rowid) from test x where t.id = x.id); 2.select distinct t.* from test t;删除重复记录 1.delete from test t where t.rowid > (select min(x.rowid) from test x where t.id = x.id); 2.delete from test t where t.rowid <> (select max(x.rowid) from test x where t.id = x.id);
from table
where rowid
in (
select rowid from
(select c1,c2,c3.....,row_number() over(partition by c1,c2,c3..... order by rowid) rn
from table)
where rn>1)
where rowid in(select rd from(
select rowid rd,row_number()over(partition by 字段 order by rownum)rn
from tt)
where rn<>1)
----------
1111
1111
2222
2222SQL> delete from ttt1 where rowid not in (select max(rowid) from ttt1 group by n
ame);SQL> select name from ttt1;NAME
----------
1111
2222
insert into tt values(1,'a','b','c');
insert into tt values(1,'a','b','c');
insert into tt values(1,'a','b','c');
insert into tt values(1,'a','b','c');
insert into tt values(2,'a1','b','c1');
insert into tt values(2,'a1','b','c1');
insert into tt values(3,'a1','b','c1');
insert into tt values(2,'a1','b','c1');
insert into tt values(3,'a1','b','c1');
insert into tt values(3,'a1','b','c1');delete from tt
where exists
(select rid
from(
select id,a,b,c,row_number() over(partition by id,a,b,c order by rowid) rn,rowid rid
from tt) b where tt.rowid=b.rid and rn>1)commit;
不过,同等条件下,应该还是通过rowid的方式快些吧,子查询的部分,相差应该不大。
刚回复完就琢磨效率的问题
分析函数和分组的比较,数据量不是非常大的话效率应该差不多的
语句要简洁得多
很多关于这样操作的资料上都提供了这样的两种方式。
保留最小
delete from a where
rowid
not in
(
select min(rowid) from a
group by XXX
);
1.select * from test t where t.rowid > (select min(x.rowid) from test x where t.id = x.id);
2.select * from test t where t.rowid <> (select max(x.rowid) from test x where t.id = x.id);
3.select count(*),t.id, t.name from test t group by id,name having count(*) > 1;查询不相同的记录
1.select * from test t where t.rowid <= (select min(x.rowid) from test x where t.id = x.id);
2.select distinct t.* from test t;删除重复记录
1.delete from test t where t.rowid > (select min(x.rowid) from test x where t.id = x.id);
2.delete from test t where t.rowid <> (select max(x.rowid) from test x where t.id = x.id);