我正在为斯坦福大学的数据库课程做介绍,这是家庭作业之一。我的代码可以很好地完成这项工作,但是我真的不喜欢如何重复使用相同的SELECT-FROM-JOIN部分两次:SELECT name, gradeFROM HighschoolerWHERE 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);我不得不查询所有只有同一个年级的朋友而没有其他年级的所有高中生。有没有办法以某种方式只编写一次下面的代码,并针对两个不同的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代码。 (adsbygoogle = window.adsbygoogle || []).push({}); 最佳答案 这是WHERE EXISTS查询的“海报子级”示例:SELECT name, gradeFROM Highschooler MEWHERE 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)添加到查询中。 (adsbygoogle = window.adsbygoogle || []).push({});
10-07 19:17
查看更多