SQL -先从课程过滤，然后从科目过滤

Question

我需要有关此问题的帮助：

查询显示所有参加COSC121、COSC126和MATH111三门课程的CIS或UT学生的学生ID、姓名和课程。按学生ID排序结果。提示：考虑使用自连接。

以下是表格：

学生学号学生姓名计划

成绩学生_ID课程_ID成绩

这是我目前的情况。

SELECT s.student_id, s.stu_name, s.program
FROM students s, grades g
WHERE s.student_id = g.student_id AND
(program = 'CIS' OR program = 'UT') AND
course_id = 'COSC126'
ORDER BY student_id;

如果我将行course_id = “COSC126” 更改为 （course_id = “COSC121” 并且 course_id = “COSC126”，course_id = “MATH111”），我不会得到任何结果。提前感谢您！

Answer 1

这是您的查询

select s.student_id, s.stu_name, s.program
from students s
inner join grades g on s.student_id = g.student_id 
inner join students t1 on t1.student_id = s.student_id and t1.course_id = 'COSC121'
inner join students t2 on t2.student_id = s.student_id and t2.course_id = 'COSC126'
inner join students t3 on t3.student_id = s.student_id and t3.course_id = 'MATH111'
where 
 s.program in ('CIS', 'UT') or t1.program in ('CIS', 'UT') or t2.program in ('CIS', 'UT')
or t3.program in ('CIS', 'UT')

Answer 2

根据自连接提示，我们可以尝试：

SELECT
    s1.student_id,
    s1.stu_name,
    s1.program
FROM students s1
INNER JOIN students s2
    ON s1.student_id = s2.student_id
INNER JOIN students s3
    ON s1.student_id = s3.student_id
WHERE
    s1.program IN ('CIS', 'UT') AND
    s1.course_id = 'COSC121' AND
    s2.course_id = 'COSC126' AND
    s3.course_id = 'MATH111';

不过，我通常使用聚合方法来解决此类问题：

SELECT
    student_id,
    stu_name,
    program
FROM students
WHERE
    program IN ('CIS', 'UT') AND
    course_id IN ('COSC121', 'COSC126', 'MATH111')
GROUP BY
    student_id,
    stu_name,
    program
HAVING
    COUNT(DISTINCT course_id) = 3;