Mysql 如何在sql中挑出某些名称

发布于02月24日

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

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;

我试图实现的上述关系应该很好地列出学生申请的所有学校和专业.

Mysql 如何在sql中挑出某些名称

推荐答案

Mysql相关问答推荐

根据计数按月和年对数据进行分组()

约会时的意外行为

如何合并有多行的json列

Mysql：使用like Select 查询

错误 2020 (HY000)：数据包大于 'max_allowed_packet' 字节，docker 容器内出现 mysql 错误

外部磁盘上的MySQL表空间和数据文件

GoRM中行最大值查询返回"0"

将 JSON 类型的列与特定字符串值进行比较

谁能帮我优化低性能的mysql查询

为什么这个查询需要超过 5 秒才能运行？

使用 SET 变量进行 MySQL 查询

在分组结果 MySQL 5.7 版中获取最多的重复值

正则表达式模式相当于 mysql 中的 %word%

终止空闲的mysql连接

在 WHERE 子句中使用 CASE

如何在mysql select查询中获取两个日期之间的日期列表

如何通过一个查询批量更新 mysql 数据？

如何使用 phpmyadmin 复制数据库？

在 Ubuntu 上安装 mysql gem 的困难

如果另一列为空，则 Select 一列