备注:测试数据库版本为MySQL 8.0
测试数据:
drop table t2;
create table t2(test1 int,test2 int);
insert into t2 values (20,20);
insert into t2 values (50,25);
insert into t2 values (20,20);
insert into t2 values (60,30);
insert into t2 values (70,90);
insert into t2 values (80,130);
insert into t2 values (90,70);
insert into t2 values (100,50);
insert into t2 values (110,55);
insert into t2 values (120,60);
insert into t2 values (130,80);
insert into t2 values (140,70);
一.需求
一个表中包含两次考试的结果,要确定哪些分数对是互换的。
互换的概念 test1 和 test2的值对调
下面列出表t2的结果集:
mysql> select * from t2;
±------±------+
| test1 | test2 |
±------±------+
| 20 | 20 |
| 50 | 25 |
| 20 | 20 |
| 60 | 30 |
| 70 | 90 |
| 80 | 130 |
| 90 | 70 |
| 100 | 50 |
| 110 | 55 |
| 120 | 60 |
| 130 | 90 |
| 140 | 70 |
±------±------+
12 rows in set (0.00 sec)
检验这些结果,会发现 test1 70和 test2 90的考试分数是互换的(存在test1 90 和 test2 70的行);
要求每对互换行仅找出一个互换对,其结果集应如:
test1 test2
20 20
70 90
80 130
而不是:
test1 test2
20 20
20 20
70 90
90 70
80 130
130 80
二.解决方案
代码:
select distinct v1.*
from t2 v1,t2 v2
where v1.test1 = v2.test2
and v1.test2 = v2.test1
and v1.test1 <= v1.test2;
测试记录:
mysql> select distinct v1.*
-> from t2 v1,t2 v2
-> where v1.test1 = v2.test2
-> and v1.test2 = v2.test1
-> and v1.test1 <= v1.test2;
+-------+-------+
| test1 | test2 |
+-------+-------+
| 20 | 20 |
| 70 | 90 |
| 80 | 130 |
+-------+-------+
3 rows in set (0.00 sec)