SQL 识别互逆的记录|极客教程

SQL 识别互逆的记录，你有一张表，其中包括了两次考试的成绩，你想找出哪些分数互逆的（reciprocal）。我们先看一下下面视图 V 的结果集。

SQL 识别互逆的记录问题描述

你有一张表，其中包括了两次考试的成绩，你想找出哪些分数互逆的（reciprocal）。我们先看一下下面视图 V 的结果集。

select *
  from V
　
TEST1      TEST2
----- ----------
   20         20
   50         25
   20         20
   60         30
   70         90
   80        130
   90         70
  100         50
  110         55
  120         60
  130         80
  140         70

仔细看上述结果，可以发现 TEST1 等于 70 的考试分数和 TEST2 等于 90 的考试分数是互逆的（TEST1 存在一个 90 的分数，并且 TEST2 也存在一个 70 的分数）。同样地，TEST1 等于 80 的分数和 TEST2 等于 130 的分数也是互逆的，因为 TEST1 里有 130，TEST2 里也有 80。并且，TEST1 等于 20 的分数和 TEST2 等于 20 的分数也是互逆的，因为同样存在 TEST2 等于 20、并且 TEST1 等于 20 的记录。你只希望识别出互逆的记录的集合。因此，你希望得到的结果集如下所示。

TEST1      TEST2
----- ----------
   20         20
   70         90
   80        130

而不是下面的这个。

TEST1      TEST2
----- ----------
   20         20
   20         20
   70         90
   80        130
   90         70
  130         80

SQL 识别互逆的记录解决方案

使用自连接识别出 TEST1 等于 TEST2，并且 TEST2 等于 TEST1 的那些行。

select distinct v1.*
  from V v1, V v2
 where v1.test1  = v2.test2
   and v1.test2  = v2.test1
   and v1.test1 <= v1.test2

SQL 识别互逆的记录扩展知识

自连接产生了一组笛卡儿积，这样一个 TEST1 分数可以和每一个 TEST2 分数进行比较；反之，一个 TEST2 分数也可以和每一个 TEST1 分数进行比较。下面的查询将会识别出互逆的分数。

select v1.*
  from V v1, V v2
 where v1.test1 = v2.test2
   and v1.test2 = v2.test1
　
TEST1      TEST2
----- ----------
   20         20
   20         20
   20         20
   20         20
   90         70
  130         80
   70         90
   80        130

使用 DISTINCT 能确保从最后的结果集里删除掉重复项。WHERE 子句的最后一个过滤条件（and V1.TEST1 <= V1.TEST2）将确保只返回 TEST1 小于或等于 TEST2 的那一对互逆的分数。