oracle SQL：在其所有记录中不包含其他特定值的情况下选择不同的值

Question

这是我拥有的当前数据库记录，我只希望它显示所有从未登记科学的学生的姓名。

Name  | Subject  | Year 
----------------------------------- 
Ian Lee  | Math  | 2008 
Ian Lee  | Science  | 2008 
Ian Lee  | Econs  | 2006 
Marie-Ann | Geography | 2006 
Marie-Ann | Literature | 2009 
Natalie S. | Geography | 2006 
Julienne | Math  | 2008 
Julienne | Science  | 2008 
Julienne | Literature | 2009 
Liam  | Literature | 2009 
Liam  | Econs  | 2006 

我还有一个学生记录Emily Toh还没有在任何课程中注册。但是，正确的输出应该是

Name 
------------ 
Marie-Ann 
Natalie S. 
Emily Toh 
Liam 

这是我用来调用

SELECT DISTINCT en.Name 
FROM ENROLLMENT en 
WHERE NOT EXISTS (
    SELECT st.Name 
    FROM STUDENT st 
    WHERE en.Name = st.Name 
    AND en.Subject = 'Science' 
); 

但它仍然给了我所有的学生姓名的显示。

学生表和enrollemnt表是按照：

CREATE TABLE STUDENT(
    Name  VARCHAR2(50), 
    DOB  DATE, 
    Address VARCHAR(70), 
CONSTRAINT STUDENT_PKEY PRIMARY KEY (Name) 
); 

CREATE TABLE ENROLLMENT(
    Name  VARCHAR2(50), 
    Subject VARCHAR2(70), 
    Year  Number(4), 
CONSTRAINT ENROLLMENT_PK PRIMARY KEY (Name, Subject) 
CONSTRAINT ENROLLMENT_FKEY FOREIGN KEY (Name) REFERENCES TO STUDENT (Name) 
); 

Answer 1

你做的方法是正确的，只是STUDENTS表开始，并查找使用NOT EXISTS科学招生。

SELECT st.Name 
FROM STUDENT st 
WHERE NOT EXISTS (
    SELECT st.Name 
    FROM enrollment en 
    WHERE en.Name = st.Name 
    AND en.Subject = 'Science' 
); 

Answer 2

一种选择是使用子查询来识别所有的学生，你不希望在您的结果集（即那些入选谁在科学的某个时刻），然后加入来滤除这些学生。

SELECT s.Name  -- , s.Id 
FROM STUDENT s 
LEFT JOIN 
(
    SELECT Name -- , Id 
    FROM ENROLLMENT 
    WHERE Subject = 'Science' 
) t 
    ON s.Name = t.Name -- AND s.Id = t.Id 
WHERE t.Name IS NULL 

这种方法将捕获谁是就读于没有类和学生谁入学，但在科学从未招收两名学生。

理想情况下，还应该有一个Id列与两个表中的每个学生相关联。如果没有这一点，如果两个或两个以上的学生分享同一个名字，加入一个有意义的方式可能会很困难。

Answer 3

如果您不需要担心任何课程都没有参加谁的学生，那么你可以使用GROUP BY ... HAVING ...这样的：

SELECT name 
FROM  enrollment 
GROUP BY name 
HAVING COUNT(CASE WHEN subject = 'Science' THEN 1 ELSE NULL END) = 0; 

如果你还没有报名参加任何班级谁的学生，你想它们包括在输出则：

SELECT s.name 
FROM  student s 
     LEFT OUTER JOIN 
     enrollment e 
     ON (s.name = e.name AND e.subject = 'Science') 
GROUP BY s.name 
HAVING COUNT(e.subject) = 0; 

name由于和subject是用于表则GROUP BY和HAVING子句可以用0来替换复合主键检查：

SELECT s.name 
FROM  student s 
     LEFT OUTER JOIN 
     enrollment e 
     ON (s.name = e.name AND e.subject = 'Science') 
WHERE e.subject IS NULL;