In Oracle, determine which rows in the table are interchangeable.

A Test Table V is created, which contains two columns: test1 and Test2. It is required that the two columns of data be interchangeable rows.

SQL> select * from V;

Test1 Test2
--------------------
20 20
10 20
20 10
30 50
50 30
60 70
70 80

20 20

 

SQL> select distinct V1 .*
2 from V V1, V v2
3 where v1.test1 = v2.test2
4 and v1.test2 = v2.test1
5 and v1.test1 <= v1.test2;

Test1 Test2
--------------------
10 20
30 50
20 20

 

The role of distinct is to remove duplicate rows. Let's take it out and check the result.

SQL> select V1 .*
2 from V V1, V v2
3 where v1.test1 = v2.test2
4 and v1.test2 = v2.test1
5 and v1.test1 <= v1.test2;

Test1 Test2
--------------------
20 20
20 20
10 20
30 50
20 20
20 20

 

The last where condition v1.test1 <= v1.test2 is used to ensure that only one pair of SWAps is returned. For example, if there are 30 50 records and 50 30 records, only 30 50 records are returned. Take it and check the result.

SQL> select distinct V1 .*
2 from V V1, V v2
3 where v1.test1 = v2.test2
4 and v1.test2 = v2.test1;

Test1 Test2
--------------------
10 20
50 30
30 50
20 20
20 10