如何去除多余记录

[willdavis]

先有表 如下
id A B
10001 0403a-6-13 0516a-1-1
10002 0516a-1-1 0403a-6-13
A 和B两端相同的话 ，即为重复，需要删除10002。请各位指点一下

[wh62592855]

有没有什么要求呢

比如说是删除id为10001的记录还是删除id为10002的记录

[willdavis]

也没有特别的要求，就是删除多余的，只留一条

[iqlife]

select min(id),a,b
from table
group by a,b

[willdavis]

这样好像不能去除重复数据

[iqlife]

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
)

[shehasgone]

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
;

[shehasgone]

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)
;

[luoyoumou]

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 );

[luoyoumou]

-- 两端相同：加个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 );

[lzbbob1985]

group by 所有字段 也可以

[luoyoumou]

-- 正确答案：
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 );

[xdy3008]

[Quote=引用 11 楼 luoyoumou 的回复:]
SQL code
-- 正确答案：
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……
[/Quote]
..

[mahanso]

如果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)

[xuesongliu]

保留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));

[璇之星]

[Quote=引用楼主 willdavis 的回复:]
先有表 如下
id A B
10001 0403a-6-13 0516a-1-1
10002 0516a-1-1 0403a-6-13
A 和B两端相同的话 ，即为重复，需要删除10002。请各位指点一下
[/Quote]
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 );

2 rows deleted

19:55:20 SQL> select * from tb;

ID A B
--- ---------- ----------
1 123 345

19:55:30 SQL>