A B C
1 2 11
1 3 4
2 1 2
3 2 111
3 1 22
2 3 555
5 2 55
6 1 22
.......
请问:如何查处如下数据,也就是不用管C列的值,A列中的值,在另外行中等于B列中的值,且B列中的值在另外行中等于A列的
值
A B C
1 2 11
2 1 2
3 1 22
1 3 4
3 2 111
2 3 555
.......
6 个解决方案
#1
select a.* from tablename a left join tablename b on a.a=b.b
#2
select * from tb
where
exists (select 1 from tb as tb1 where tb.A=tb1.B and tb.B=tb1.A)
#3
select * from tb t
where exists(select 1 from tb where a=t.b)
and exists(select 1 from tb where b=t.a)
#4
select * from tb a
where
exists (select 1 from tb b where a.A=b.B and a.B=b.A)
#5
select
*
from
tb t
where
exists (select 1 from tb where A=t.B and B=t.A)
#6
那怎样才能删除其中重复的记录呢?只保留一条
#1
select a.* from tablename a left join tablename b on a.a=b.b
#2
select * from tb
where
exists (select 1 from tb as tb1 where tb.A=tb1.B and tb.B=tb1.A)
#3
select * from tb t
where exists(select 1 from tb where a=t.b)
and exists(select 1 from tb where b=t.a)
#4
select * from tb a
where
exists (select 1 from tb b where a.A=b.B and a.B=b.A)
#5
select
*
from
tb t
where
exists (select 1 from tb where A=t.B and B=t.A)
#6
那怎样才能删除其中重复的记录呢?只保留一条