多表查询时MySQL返回了重复结果

Question

如果这个问题有点模糊，请告诉我，我会提供更多信息。

我编写了一个查询，从多个表中获取数据，但它的工作方式并不像我预期的那样，我完全被卡住了。

以下是我的代码：

SELECT students.student_fname, students.student_lname
FROM  students, enrolments
WHERE enrolments.courseID = 'C001';

但是这只返回了students表中所有学生的名字和姓氏，并且这些名字会重复显示两次。

以下是两张表格的代码：

CREATE TABLE students
(
studentID CHAR(10) NOT NULL,
student_fname VARCHAR(15) NOT NULL,
student_lname VARCHAR(15) NOT NULL,
DOB VARCHAR(10) NOT NULL,
CONSTRAINT pk_students PRIMARY KEY (studentID)
);

CREATE TABLE enrolments
(
enrolmentNo int NOT NULL AUTO_INCREMENT,
studentID CHAR(10) NOT NULL,
courseID CHAR(4) NOT NULL,
CONSTRAINT pk_enrolments PRIMARY KEY (enrolmentno),
FOREIGN KEY (studentID) REFERENCES students (studentID),
FOREIGN KEY (courseID) REFERENCES courses (courseID)
)ENGINE = INNODB;

- Michael

---

了解这两个表之间的表结构以及它们之间的“关系”将有助于更好地支持您的问题。可以这样想，系统有一个包含所有学生和所有注册的列表，因为您没有告诉系统它们之间的关系，所以它假定所有注册都是针对每个学生的。 - xQbert

added those into the question - Michael

根据表设计，我没有看到StudentID、enrolmentNo和courseID上的唯一索引。因此，一个学生可以多次注册同一门课程，因此使用distinct/group by会有用。(或者如果某人通过/失败并重新参加...在不同的学期，那么需要一个年份/学期的注册表，这些信息是明智的... - xQbert

那么我该如何创建一个查询来实现我的目标呢？我想要返回选修课程ID为C001的学生的名字和姓氏。但是当我运行查询时，它只会返回所有结果。我完全不知所措。 - Michael

Answer 1

这是因为您尚未定义学生与注册之间的关系。您需要使用内连接或添加where子句以显示它们如何相关联。

例如：

FROM Students
INNER JOIN enrolments on Students.ID = enrolments.studentID

或者

FROM students, enrolements 
WHERE enrolments.studentID = students.ID

第一种方法是较新的，被许多人所偏好；但也支持传统的方法。

要更好地理解表连接，请查看这篇最棒的文章：http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html 否则，您将获得所谓的两个表的笛卡尔积，所有数据都与所有数据相关联。

如果您在enrolements上没有唯一索引（一个学生只能有一个班级注册），那么select Distinct field names或where... group by fields也将限制您的结果接近你所期望的。

SELECT S.student_fname, S.student_lname 
FROM students S 
INNER JOIN enrolments  E
  ON S.StudentID = E.StudentID
WHERE e.courseID = 'C001'
GROUP BY S.Student_Fname, S.Student_lname
ORDER BY S.Student_LName, S.Student_FName

根据课程分组，可以消除重复。 "ON语句告诉数据库学生与注册如何关联。 Order by只是为了提供合理的结果顺序。

- xQbert

---

如果学生到注册表上没有唯一索引，那么这可能是正确的。但是，如果系统限制了注册重复，基于特定课程的限制，这将不是一个问题。 - xQbert

非常感谢，看到它是如何工作的帮助我理解了它。谢谢！ - Michael

Answer 2

你不能用这种方式从两个表中提取数据，必须使用联接来连接表。

- Nik Drosakis