现实中总会遇到这样的问题：
ID NAME1 NAME2
1001 AB BA
1002 AC CA
1003 BA AB
1004 CA AC
1001 AB BA
1002 AC CA
1003 BA AB
1004 CA AC
上述中的第一行记录和第三行记录被认为是相同的，要删除其中一条，怎么办呢？
方法一：利用greatest、least、rowid
drop table t_ab;
create table t_ab (id number(5,0),name1 varchar2(10),name2 varchar2(10));
insert into t_ab values(1001,'AB','BA');
insert into t_ab values(1002,'AC','CA');
insert into t_ab values(1003,'BA','AB');
insert into t_ab values(1004,'CA','AC');
insert into t_ab values(1002,'AC','CA');
insert into t_ab values(1003,'BA','AB');
insert into t_ab values(1004,'CA','AC');
commit;
select * from t_ab t;
delete from t_ab t
where exists (select 1 from t_ab t1
where t.name1=greatest(t1.name1,t1.name2)
and t.name2=least(t1.name1,t1.name2)
and t.rowid<>t1.rowid)
where exists (select 1 from t_ab t1
where t.name1=greatest(t1.name1,t1.name2)
and t.name2=least(t1.name1,t1.name2)
and t.rowid<>t1.rowid)
方法二：做一次连接，利用not in
delete from t_ab t
where rowid in
(select least(t1.rowid,t2.rowid) from t_ab t1,t_ab t2
where t1.name1=t2.name2 and t1.name2=t2.name1)
点评：方法一效率可能更高点。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/26977915/viewspace-734068/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
