如何从查询结果中删除反向重复项？

beginql

我从Lagunita Stanford解决了SQL练习，然后停在了第3个问题（SQL社交网络查询练习）上。我不知道如何从查询结果中删除反向重复：

    Gabriel    9  |  Cassandra  9
    ------------------------------
    Cassandra  9  |  Gabriel    9
    ------------------------------
    Jessica    11 |  Kyle       12
    ------------------------------
    Kyle       12 |  Jessica    11

答案应该是：

        Cassandra  9  |  Gabriel    9
        ------------------------------
        Jessica    11 |  Kyle       12

这是我的代码：

SELECT h1.name,
   h1.grade,
   h2.name,
   h2.grade          
FROM likes AS likes1
INNER JOIN likes AS likes2
   ON likes1.id1 = likes2.id
   AND likes1.id2 = likes2.id1
INNER JOIN highschooler  AS h1 ON likes1.id1 = h1.id
INNER JOIN highschooler  AS h2 ON likes2.id1 = h2.id

表格：https：//lagunita.stanford.edu/c4x/DB/SQL/asset/socialdata.html

而且我不能使用窗口函数和CTE。

我很好奇这是否可以删除同一查询中的重复项。

去世

当前的结果集形成时，每对包含两次，而要删除两行之一，您需要做的就是应用一个WHERE子句：

SELECT h1.name name1,
       h1.grade grade1,
       h2.name name2,
       h2.grade grade2
FROM likes AS likes1
INNER JOIN likes AS likes2 ON likes1.id1 = likes2.id2 AND likes1.id2 = likes2.id1
INNER JOIN highschooler  AS h1 ON likes1.id1 = h1.id
INNER JOIN highschooler  AS h2 ON likes2.id1 = h2.id
WHERE h1.name < h2.name

或者可能是：

WHERE likes1.id1 < likes1.id2

如果有2名高中生同名。
参见演示。
结果：

| name1     | grade1 | name2   | grade2 |
| --------- | ------ | ------- | ------ |
| Cassandra | 9      | Gabriel | 9      |
| Jessica   | 11     | Kyle    | 12     |

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。