这是我现有的数据库记录，我只希望它显示所有从未注册过 Science 的学生的姓名。
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'
);
但它仍然让我显示所有学生的名字。
学生表和注册表如下：
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)
);
您采用的方法是正确的，只需从STUDENTStable开始，然后使用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'
);
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句