delete from 1张表 a where exists (select 1 from 1张表 where 数据b=a.数据b and 数据c=a.数据c and 数据a<a.数据a)
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); 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)
相似的问题csdn有很多解答过的。。
<pre>select * from tb a join( select repeatColumnA,repeatColumnB,repeatColumnN from tb group by repeatColumnA,repeatColumnB,repeatColumnN having count(*)>1 )b on a.repeatColumnA=b.repeatColumnA and a.repeatColumnB=b.repeatColumnB and a.repeatColumnN=b.repeatColumnN;</pre>
select min(id) id,b,c from tb group by b,c--删除重复数据:
delete from tb where rowid not in (select min(rowid) from tb group by b,c);
oracle 中rowid(伪列) 用来唯一标示一行记录
上面的语句按 b, c列分组,并找出每组最小的rowid, 然后其它行的数据删除。
where exists (select 1 from 1张表 where 数据b=a.数据b and 数据c=a.数据c and 数据a<a.数据a)
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);
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)
select repeatColumnA,repeatColumnB,repeatColumnN from tb
group by repeatColumnA,repeatColumnB,repeatColumnN
having count(*)>1
)b on a.repeatColumnA=b.repeatColumnA
and a.repeatColumnB=b.repeatColumnB
and a.repeatColumnN=b.repeatColumnN;</pre>