mysql - 关于mysql子查询的问题

Question

首先，这是作业的一部分，但我正在尝试更清楚地了解子查询在初始查询方面的工作原理。

问题是列出在他或她完成的所有类(class)中获得 A 的每个学生的姓名和 SSN，即没有 Null 成绩，每个成绩都是 A。

我假设这可以通过子查询完成是否正确？或者我应该使用一些外部连接方法来合并所有类(class)中只有 A 的学生。我不确定哪个应该是嵌套查询和外部查询。我尝试这样做:

SELECT *
From Student S JOIN Enrollment E
WHERE S.SSN = E.SSN AND E.Grade = "A"
ORDER BY E.CourseNumber

作为我的嵌套查询，它将返回所有获得 A 的学生，但问题是询问哪些学生在所有类(class)中都获得了 A。不确定我会进行什么外部查询。使用它需要我将一些东西作为选择，因为我显然不能将多个变量相互比较，而且我也不确定在那里比较什么。

这是数据库的一些上下文:

CREATE TABLE Student(
SSN INT(9),
Name VARCHAR(20),
Major VARCHAR(30),
PRIMARY KEY (SSN)
);

CREATE TABLE Course(
CourseNumber INT(5),
PrerequisiteCourseNumber INT(10),
CourseTitle VARCHAR(10),
NumberUnits INT(2),
PRIMARY KEY (CourseNumber)
);

CREATE TABLE Section(
CourseNumber INT(5),
Quarter VARCHAR(10),
RoomNumber INT(5),
DayTime VARCHAR(20),
PRIMARY KEY (CourseNumber,Quarter),
FOREIGN KEY (CourseNumber) REFERENCES Course(CourseNumber)
);


CREATE TABLE Enrollment(
SSN INT(9),
CourseNumber INT(5),
Quarter VARCHAR(10),
Grade VARCHAR(1),
PRIMARY KEY (SSN,CourseNumber,Quarter),
FOREIGN KEY (SSN) REFERENCES Student(SSN),
FOREIGN KEY (CourseNumber) REFERENCES Course(CourseNumber),
FOREIGN KEY (Quarter) REFERENCES Section(Quarter)
);

Answer 1

架构

CREATE TABLE Student(
SSN INT(9),
Name VARCHAR(20),
Major VARCHAR(30),
PRIMARY KEY (SSN)
);

CREATE TABLE Enrollment(
SSN INT(9),
CourseNumber INT(5),
Quarter VARCHAR(10),
Grade VARCHAR(1),
PRIMARY KEY (SSN,CourseNumber,Quarter),
FOREIGN KEY (SSN) REFERENCES Student(SSN)
-- FOREIGN KEY (CourseNumber) REFERENCES Course(CourseNumber),
-- FOREIGN KEY (Quarter) REFERENCES Section(Quarter)
);

insert student(ssn,name,major) values
(1,'John','b'),(2,'Sally','b'),(3,'Kim','b'),(4,'Stan','b');

insert enrollment(ssn,coursenumber,quarter,grade) values
(1,1,'F2015','A'),
(2,1,'F2015','A'),(2,2,'F2015','B'),
(3,1,'F2015','B'),
(4,1,'F2015','A'),(4,2,'F2015',null),(4,3,'F2015','A');

查询

select distinct s.ssn,s.name
from
(   select ssn,count(ssn) outCount
    from
    (   select e.ssn,e.grade,count(e.ssn) theCountInner
        from enrollment e
        where e.grade is not null
        group by e.ssn,e.grade
    ) xInner1
    -- where count(ssn)=1
    group by ssn
    having outCount=1
) xInner2
join student s
on s.ssn=xInner2.ssn
join enrollment
on enrollment.ssn=xInner2.ssn
and enrollment.grade='A'
+-----+------+
| ssn | name |
+-----+------+
|   1 | John |
|   4 | Stan |
+-----+------+

请注意，distinct 用于为 Stan 删除两行，如果没有它，结果将是这样。

请注意，xInner1 派生表会返回没有空值的成绩，其中包含 SSN 级别的计数。

这是对 having=1 的 SSN 的快速重新计数。这成为派生表 xInner2。

这与 student 和 enrollment 一起找到了 A。如前所述，distinct 会清理它，这样 Stan 就不会出现两次。

我相信还有其他方法。这是我的技巧