select id,a,b from tab where id not in( --选出重复的大的ID select max(t.id) from (select id,a,b from tab union all select id,b as a,a as b from tab) t group by t.a,t.b having count(*)>1 )
select f.t,min(f.id) from ( select id,case when a.a>a.b then a.a||a.b else a.b||a.a end t from tab a) f group by f.t ;
delete from tab e where e.id not in (select min(f.id) from ( select id,case when a.a>a.b then a.a||a.b else a.b||a.a end t from tab a) f group by f.t) ;
delete from tb t1 where not exists ( select 1 from tb t2 where t2.a=t1.a and t2.b=t1.b group by t2.a, t2.b having min(id)=t1.id );
-- 两端相同:加个or条件 delete from tb t1 where not exists ( select 1 from tb t2 where (t2.a=t1.a and t2.b=t1.b) or (t2.a=t1.b and t2.b=t1.a) group by t2.a, t2.b having min(id)=t1.id );
group by 所有字段 也可以
-- 正确答案: delete from tb t1 where exists ( select 1 from tb t2 where ((t2.a=t1.a and t2.b=t1.b) or (t2.a=t1.b and t2.b=t1.a)) and t2.id>t1.id );
如果id列唯一的话,保留小的id值数据:delete from test1 t1 where t1.id > (select min(id) from test1 t2 where t1.a = t2.a and t1.b = t2.b) and exists (select 1 from test1 t2 where t1.a = t2.a and t1.b = t2.b)
保留ROWID最大的记录,其余全部删除delete from table a where a.rowid!=(select max(rowid) from a b where (a.a=b.a and a.b=b.b) or (a.a=b.b and a.b=b.a));
delet tablename where id in (select (case when T001.A=T002.B and T001.B=T002.B then T002.ID end) deletID from tableName T001 ,tableName T002 where T001.Id=T002.ID)
19:47:42 SQL> select * from tb;
ID A B --- ---------- ---------- 2 345 123 1 123 345 3 123 345
19:47:46 SQL> delete from tb where id in 2 ( 3 select distinct(aa.id) from tb aa,tb bb 4 where aa.a=bb.b and bb.a=aa.b and aa.a<>bb.a 5 and aa.id<>( 6 select min(aa.id) from tb aa,tb bb 7 where aa.a=bb.b and bb.a=aa.b and aa.a<>bb.a 8 ) 9 );
from table
group by a,b
from tab
where id not in(
--选出重复的大的ID
select max(t.id)
from
(select id,a,b from tab
union all
select id,b as a,a as b from tab) t
group by t.a,t.b
having count(*)>1
)
select id,case when a.a>a.b then a.a||a.b else a.b||a.a end t from tab a) f
group by f.t
;
select id,case when a.a>a.b then a.a||a.b else a.b||a.a end t from tab a) f
group by f.t)
;
where not exists ( select 1
from tb t2
where t2.a=t1.a and t2.b=t1.b
group by t2.a, t2.b
having min(id)=t1.id );
delete from tb t1
where not exists ( select 1
from tb t2
where (t2.a=t1.a and t2.b=t1.b) or (t2.a=t1.b and t2.b=t1.a)
group by t2.a, t2.b
having min(id)=t1.id );
delete from tb t1
where exists ( select 1
from tb t2
where ((t2.a=t1.a and t2.b=t1.b) or (t2.a=t1.b and t2.b=t1.a))
and t2.id>t1.id );
where t1.id > (select min(id)
from test1 t2
where t1.a = t2.a
and t1.b = t2.b)
and exists (select 1
from test1 t2
where t1.a = t2.a
and t1.b = t2.b)
where a.rowid!=(select max(rowid) from a b where (a.a=b.a and a.b=b.b) or (a.a=b.b and a.b=b.a));
19:47:42 SQL> select * from tb;
ID A B
--- ---------- ----------
2 345 123
1 123 345
3 123 345
19:47:46 SQL> delete from tb where id in
2 (
3 select distinct(aa.id) from tb aa,tb bb
4 where aa.a=bb.b and bb.a=aa.b and aa.a<>bb.a
5 and aa.id<>(
6 select min(aa.id) from tb aa,tb bb
7 where aa.a=bb.b and bb.a=aa.b and aa.a<>bb.a
8 )
9 );
2 rows deleted
19:55:20 SQL> select * from tb;
ID A B
--- ---------- ----------
1 123 345
19:55:30 SQL>