sql 去除重复记录

排除左右交叉相同的记录：
create table Ta as(
select 'a' f1,'b' f2 from dual union all
select 'b' f1,'a' f2 from dual union all
select 'q' f1,'p' f2 from dual union all
select 'm' f1,'n' f2 from dual union all
select 'n' f1,'m' f2 from dual );

①：
select * from Ta
minus  --集合相减
select t1.f1,t1.f2 from ta t1,ta t2
where t1.f1=t2.f2 and t1.f2=t2.f1 and t1.f1>t2.f1;
②
选出相同的结果，与原表连接 exists
select * from Ta 
where not exists
(select 1 from
(select t1.f1,t1.f2 from ta t1,ta t2
where t1.f1=t2.f2 and t1.f2=t2.f1 and t1.f1>t2.f1 )b
where b.f1=Ta.f1) 
;

③
如果左右相同的话，ascii码相加的和是一样的，所以按ascii相加的和分组排序 a+z与 b+y是相等的 会出错
select f1,f2 from (
select f1,f2, row_number() over(partition by ascii(f1)+ascii(f2)  order by ascii(f1)+ascii(f2)) rn from Ta)t1
where rn=1
;


去除重复记录
①无id 直接distinct
②1列id，一列数据：
select * from t4;
id f1
1 a
2 a
3 q
4 m
5 n
select id,f1 from (
select id,f1,
row_number() over(partition by f1 order by id) rn from t4)
where rn=1
order by id;


③1列id，多列数据：


Create table t4 as
(
select 1 id,'a' f1,'b' f2 from dual union all
select 2,'a' f1,'b' f2 from dual union all
select 3,'q' f1,'p' f2 from dual union all
select 4,'m' f1,'n' f2 from dual union all
select 5,'n' f1,'m' f2 from dual );


select id,f1,f2 from(
select id,f1,f2,row_number() over(partition by f1,f2 order by f1,f2) rn from t4)
where rn=1
order by id;


删除：
delete from t4 where id in(
select id from (
select id,f1,f2,row_number() over(partition by f1,f2 order by f1,f2) rn from t4
)
where rn>1
)