子查询与一题多解

查询与“王海强”同一班级的其他学生信息

连接查询

SELECT s2.*  
FROM Students s1  
JOIN Students s2 ON s1.MajorClass = s2.MajorClass  
WHERE s1.Name = N'王海强' AND s2.Name != N'王海强';

• WHERE s1.Name = N'王海强' AND s2.Name != N'王海强';：s1 代表王海强，s2 代表其他学生，
• JOIN Students s2 ON s1.MajorClass = s2.MajorClass：将 Students 表自连接

子查询

SELECT *  
FROM Students  
WHERE MajorClass = (SELECT MajorClass FROM Students WHERE Name = N'王海强')  
  AND Name != N'王海强';

• 子查询 (SELECT MajorClass FROM Students WHERE Name = N'王海强') 获取王海强的班级
• 主查询则查询与王海强同班级但不是王海强本人的学生

查询选修课程号为K002的学生的学号、姓名

连接查询

SELECT DISTINCT s.StudentID, s.Name  
FROM Students s  
JOIN StudentAssignments sa ON s.StudentID = sa.StudentID  
WHERE sa.CourseID = 'K002';

• DISTINCT：去除可能的重复结果

普通子查询

SELECT StudentID, Name  
FROM Students  
WHERE StudentID IN (SELECT StudentID FROM StudentAssignments WHERE CourseID = 'K002');

• 子查询选择了 K002 课程的所有学生 ID
• 主查询从 Students 表中选择了这些学生的信息

相关子查询

SELECT StudentID, Name  
FROM Students s  
WHERE s.StudentID IN (  
    SELECT sa.StudentID   
    FROM StudentAssignments sa   
    WHERE sa.CourseID = 'K002' AND sa.StudentID = s.StudentID  
);

• 对每个学生 s，检查他们的 StudentID 是否在选修了 KOO2 课程的学生 ID 列表中

对于子查询：

SELECT sa.StudentID   
    FROM StudentAssignments sa   
    WHERE sa.CourseID = 'K002' AND sa.StudentID = s.StudentID

它为每个学生返回了一个结果集。如果这个结果集包含学生的 ID（即学生选修了 KOO2 课程），那么这个学生就会被主查询选中

使用 EXISTS 关键字的相关子查询

SELECT StudentID, Name  
FROM Students s  
WHERE EXISTS (SELECT 1 FROM StudentAssignments sa   
              WHERE sa.StudentID = s.StudentID AND sa.CourseID = 'K002');

• EXISTS 字句检查每个学生是否有选择 KOO2 课程的记录

查询没有选修过 K001 和 M001 任一课程的学生学号、课程号和三次成绩

子查询

SELECT sa.StudentID, sa.CourseID, sa.Assignment1Score, sa.Assignment2Score, sa.Assignment3Score  
FROM StudentAssignments sa  
WHERE sa.StudentID NOT IN (  
    SELECT DISTINCT StudentID   
    FROM StudentAssignments   
    WHERE CourseID IN ('K001', 'M001')  
);

查询学时最少的课程名

子查询

SELECT CourseName  
FROM Courses  
WHERE Hours = (SELECT MIN(Hours) FROM Courses);

使用 EXISTS 关键字的相关子查询

SELECT CourseName  
FROM Courses c1  
WHERE NOT EXISTS (  
    SELECT 1 FROM Courses c2  
    WHERE c2.Hours < c1.Hours  
);

查询 K002 课程作业 1 成绩最低的学号和成绩

SELECT StudentID, Assignment1Score  
FROM StudentAssignments  
WHERE CourseID = 'K002' AND Assignment1Score = (  
    SELECT MIN(Assignment1Score)  
    FROM StudentAssignments  
    WHERE CourseID = 'K002'  
);