mysql - 如何在sql中挑出某些名称

Question

所以我在 SQL 中遇到了这个问题，问题是:List the sId and name of students that applyed to “WSU” But not “U of O”. 我的尝试是这样的:

SELECT DISTINCT sName, 
       student.sID 
FROM Student 
LEFT JOIN Apply ON Student.sID = Apply.sID 
WHERE (cName<>'U of O' and cName = 'WSU') 
ORDER BY sName ASC;

然而，这并没有起到作用，因为它没有发现一些学生已经申请了俄勒冈大学。回复:

CREATE TABLE IF NOT EXISTS College
(
  State CHAR(2) NOT NULL,
  cName VARCHAR(20) NOT NULL,
  enrollment INT NOT NULL,
  PRIMARY KEY (cName)
);

CREATE TABLE IF NOT EXISTS Major
(
  major VARCHAR(30) NOT NULL,
  PRIMARY KEY (major)
);

CREATE TABLE IF NOT EXISTS Student
(
  sID INT NOT NULL,
  sName VARCHAR(30) NOT NULL,
  GPA FLOAT NOT NULL,
  sizeHS INT NOT NULL,
  PRIMARY KEY (sID)
);

CREATE TABLE IF NOT EXISTS MinimumGPA
(
  minGPA FLOAT NOT NULL,
  cName VARCHAR(20) NOT NULL,
  major VARCHAR(30) NOT NULL,
  PRIMARY KEY (cName, major),
  FOREIGN KEY (cName) REFERENCES College(cName),
  FOREIGN KEY (major) REFERENCES Major(major)
);

CREATE TABLE IF NOT EXISTS APPLY
(
  decision SET('Y', 'N') NOT NULL,
  sID INT NOT NULL,
  cName VARCHAR(20) NOT NULL,
  major VARCHAR(30) NOT NULL,
  PRIMARY KEY (sID, cName, major),
  FOREIGN KEY (sID) REFERENCES Student(sID),
  FOREIGN KEY (cName, major) REFERENCES MinimumGPA(cName, major)
);

INSERT INTO Student (sId, sName, GPA, sizeHS) VALUES (123, 'Amy', 3.90, 1000);
INSERT INTO Student (sId, sName, GPA, sizeHS) VALUES (234, 'Bob', 3.60, 1500);
INSERT INTO Student (sId, sName, GPA, sizeHS) VALUES (345, 'Craig', 3.50, 500);
INSERT INTO Student (sId, sName, GPA, sizeHS) VALUES (456, 'Doris', 3.90, 1000);
INSERT INTO Student (sId, sName, GPA, sizeHS) VALUES (543, 'Craig', 3.40, 2000);
INSERT INTO Student (sId, sName, GPA, sizeHS) VALUES (567, 'Edward', 2.90, 2000);
INSERT INTO Student (sId, sName, GPA, sizeHS) VALUES (654, 'Amy', 3.90, 1000);
INSERT INTO Student (sId, sName, GPA, sizeHS) VALUES (678, 'Fay', 3.80, 200);
INSERT INTO Student (sId, sName, GPA, sizeHS) VALUES (765, 'Jay', 2.90, 1500);
INSERT INTO Student (sId, sName, GPA, sizeHS) VALUES (789, 'Gary', 3.40, 800);
INSERT INTO Student (sId, sName, GPA, sizeHS) VALUES (876, 'Irene', 3.90, 400);
INSERT INTO Student (sId, sName, GPA, sizeHS) VALUES (987, 'Helen', 4.00, 800);

INSERT INTO College (cName, State, enrollment) VALUES ('Cornell', 'NY', 21000);
INSERT INTO College (cName, State, enrollment) VALUES ('MIT', 'MA', 10000);
INSERT INTO College (cName, State, enrollment) VALUES ('WSU', 'WA', 28000);
INSERT INTO College (cName, State, enrollment) VALUES ('U of O', 'OR', 25000);

INSERT INTO Major (major) VALUES ('CS');
INSERT INTO Major (major) VALUES ('EE');
INSERT INTO Major (major) VALUES ('history');
INSERT INTO Major (major) VALUES ('biology');
INSERT INTO Major (major) VALUES ('bioengineering');
INSERT INTO Major (major) VALUES ('psychology');
INSERT INTO Major (major) VALUES ('marine biology');

INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('WSU', 'CS', 3.75);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('WSU', 'EE', 3.5);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('WSU', 'history', 2.8);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('U of O', 'CS', 3.6);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('U of O', 'biology', 3.75);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('Cornell','bioengineering', 3.8);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('Cornell','CS', 3.4);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('Cornell','EE', 3.6);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('Cornell','history', 3.6);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('Cornell','psychology', 2.8);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('MIT', 'biology', 3.5);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('MIT', 'bioengineering', 3.5);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('MIT', 'CS', 3.9);
INSERT INTO MinimumGPA (cName, major, minGPA) VALUES ('MIT', 'marine biology', 3.5);

INSERT INTO Apply (sID, cName, major, decision) VALUES (123, 'Cornell', 'EE', 'Y');
INSERT INTO Apply (sID, cName, major, decision) VALUES (123, 'WSU', 'CS', 'N');
INSERT INTO Apply (sID, cName, major, decision) VALUES (123, 'WSU', 'EE', 'N');
INSERT INTO Apply (sID, cName, major, decision) VALUES (123, 'U of O', 'CS', 'Y');
INSERT INTO Apply (sID, cName, major, decision) VALUES (123, 'MIT', 'CS', 'N');
INSERT INTO Apply (sID, cName, major, decision) VALUES (234, 'U of O', 'biology', 'N');
INSERT INTO Apply (sID, cName, major, decision) VALUES (345, 'Cornell', 'bioengineering', 'N');
INSERT INTO Apply (sID, cName, major, decision) VALUES (345, 'Cornell', 'CS', 'Y');
INSERT INTO Apply (sID, cName, major, decision) VALUES (345, 'Cornell', 'EE', 'N');
INSERT INTO Apply (sID, cName, major, decision) VALUES (345, 'MIT', 'bioengineering', 'Y');
INSERT INTO Apply (sID, cName, major, decision) VALUES (543, 'MIT', 'CS', 'N');
INSERT INTO Apply (sID, cName, major, decision) VALUES (678, 'Cornell', 'history', 'N');
INSERT INTO Apply (sID, cName, major, decision) VALUES (678, 'Cornell', 'psychology', 'Y');
INSERT INTO Apply (sID, cName, major, decision) VALUES (678, 'WSU', 'history', 'Y');
INSERT INTO Apply (sID, cName, major, decision) VALUES (765, 'WSU', 'history', 'Y');
INSERT INTO Apply (sID, cName, major, decision) VALUES (876, 'MIT', 'biology', 'Y');
INSERT INTO Apply (sID, cName, major, decision) VALUES (876, 'MIT', 'marine biology', 'N');
INSERT INTO Apply (sID, cName, major, decision) VALUES (876, 'WSU', 'CS', 'N');
INSERT INTO Apply (sID, cName, major, decision) VALUES (987, 'WSU', 'CS', 'Y');
INSERT INTO Apply (sID, cName, major, decision) VALUES (987, 'U of O', 'CS', 'Y');

CREATE TABLE IF NOT EXISTS CollegeStats
(
  cName VARCHAR(20) NOT NULL,
  appCount INT NOT NULL,
  minGPA dec(3, 2) NOT NULL,
  maxGPA dec(3, 2) NOT NULL,
  PRIMARY KEY (cName)
);

当然还有最后我要处理的功能:

SELECT DISTINCT sName, 
       student.sID 
FROM Student 
LEFT JOIN Apply ON Student.sID = Apply.sID 
WHERE (cName<>'U of O' and cName = 'WSU') 
ORDER BY sName ASC;

我得到的:

'Amy', '123'
'Fay', '678'
'Helen', '987'
'Irene', '876'
'Jay', '765'

预期:

'Fay', '678'
'Irene', '876'
'Jay', '765'


SELECT * FROM Student RIGHT JOIN Apply ON Student.sID = Apply.sID ORDER BY sName ASC;
SELECT DISTINCT sName, student.sID FROM Student LEFT JOIN Apply ON Student.sID = Apply.sID WHERE (cName<>'U of O' and cName = 'WSU') ORDER BY sName ASC;

上面我试图完成的关系应该很好地列出学生申请的所有学校和专业。

Answer 1

您不需要连接。你想见学生？所以，从学生表中选择。他们应符合标准？使用 where 子句。直接使用 IN 子句:

SELECT *
FROM Student 
WHERE sid IN (SELECT sid FROM apply WHERE cname = 'WSU')
  AND sid NOT IN (SELECT sid FROM apply WHERE cname = 'U of O')
ORDER BY sName ASC;