比如表TABLE:
A B C D E
1 2 3 4 5
2 1 3 5 6
6 7 5 2 1
3 4 6 2 9
2 2 5 3 1
7 6 7 8 2
----------------------------
要求:从上面的表中挑选出A=B 并且B=A的数据结果
A B C D E
1 2 3 4 5
2 1 3 5 6
6 7 5 2 1
7 6 7 8 2
A B C D E
1 2 3 4 5
2 1 3 5 6
6 7 5 2 1
3 4 6 2 9
2 2 5 3 1
7 6 7 8 2
----------------------------
要求:从上面的表中挑选出A=B 并且B=A的数据结果
A B C D E
1 2 3 4 5
2 1 3 5 6
6 7 5 2 1
7 6 7 8 2
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([A] int,[B] int,[C] int,[D] int,[E] int)
insert [tb]
select 1,2,3,4,5 union all
select 2,1,3,5,6 union all
select 6,7,5,2,1 union all
select 3,4,6,2,9 union all
select 2,2,5,3,1 union all
select 7,6,7,8,2
---查询---
select a.a,a.b,a.c,a.d
from (select *,rn=row_number() over(order by getdate()) from tb) a
join (select *,rn=row_number() over(order by getdate()) from tb) b
on a.A=b.B AND a.B=b.A
where a.rn!=b.rn---结果---
a b c d
----------- ----------- ----------- -----------
1 2 3 4
2 1 3 5
6 7 5 2
7 6 7 8
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([A] int,[B] int,[C] int,[D] int,[E] int)
insert [tb]
select 1,2,3,4,5 union all
select 2,1,3,5,6 union all
select 6,7,5,2,1 union all
select 3,4,6,2,9 union all
select 2,2,5,3,1 union all
select 7,6,7,8,2SELECT M.* FROM tb M,tb N
WHERE M.A=N.B
AND M.B=N.A
AND M.A<>N.AA B C D E
----------- ----------- ----------- ----------- -----------
1 2 3 4 5
2 1 3 5 6
6 7 5 2 1
7 6 7 8 2(4 row(s) affected)
join tb b
on a.A=b.B and B.a=A.b
where exists( select 1 from tb b where a.a=b.b and b.a=a.b)