sql - 有没有更优雅的方式来编写这个 SQL 查询？

Question

我正在做斯坦福的 DB 入门类(class)，这是家庭作业之一。我的代码做得很好，但我不太喜欢我两次重复使用相同的 SELECT-FROM-JOIN 部分:

SELECT name, grade
FROM Highschooler
WHERE
    ID IN (
        SELECT H1.ID
        FROM Friend
        JOIN Highschooler AS H1
            ON Friend.ID1 = H1.ID
        JOIN Highschooler AS H2
            ON Friend.ID2 = H2.ID
        WHERE H1.grade = H2.grade    
    ) AND
    ID NOT IN (
        SELECT H1.ID
        FROM Friend
        JOIN Highschooler AS H1
            ON Friend.ID1 = H1.ID
        JOIN Highschooler AS H2
            ON Friend.ID2 = H2.ID
        WHERE H1.grade <> H2.grade
    )
ORDER BY grade, name

这是代码中使用的两个表的 SQL 架构:

Highschooler(ID int, name text, grade int);
Friend(ID1 int, ID2 int);

我必须查询所有只有同年级 friend 的高中生，而不是任何其他年级的 friend 。有没有办法以某种方式只编写一次下面的代码，并为两个不同的 WHERE 子句 = 和 <> 重复使用两次？

    SELECT H1.ID
    FROM Friend
    JOIN Highschooler AS H1
        ON Friend.ID1 = H1.ID
    JOIN Highschooler AS H2
        ON Friend.ID2 = H2.ID

编辑:我们需要提供 SQLite 代码。

Answer 1

这是 WHERE EXISTS 的“典型”示例查询:

SELECT name, grade
FROM Highschooler ME
WHERE EXISTS (
    SELECT 1
    FROM Friend F
    JOIN Highschooler OTHER on F.ID2=OTHER.ID
    WHERE F.ID1=ME.ID AND OTHER.Grade = ME.GRADE
)
AND NOT EXISTS (
    SELECT 1
    FROM Friend F
    JOIN Highschooler OTHER on F.ID2=OTHER.ID
    WHERE F.ID1=ME.ID AND OTHER.Grade <> ME.GRADE
)

安EXISTS条件是 true如果它的SELECT返回一行或多行；否则为 false .您需要做的就是将内部子查询与外部子查询(F.ID1=ME.ID 部分)关联，然后添加您需要的剩余约束(OTHER.Grade = ME.GRADE 或 OTHER.Grade <> ME.GRADE)您的查询。