sql - 从表中选择一个字段具有相同值的行

Question

我有一个包含这两个表的 MySQL 数据库:

Tutor(tutorId, initials, lastName, email, phone, office)
Student(studentId, initials, lastName, email, tutorId)

返回同一导师的任何学生姓名的首字母和姓氏的查询是什么？

我试过 SELECT intials, lastName FROM Student WHERE tutorId = tutorId 但这只会返回所有学生的姓名。

Answer 1

你必须加入学生反对自己:

SELECT s1.initials, s1.lastName
FROM Student s1, Student s2
WHERE s1.studentId <> s2.studentID /* Every student has the same tutor as himself */
AND s1.tutorId = s2.tutorid

如果你想输出对:

SELECT s1.initials, s1.lastName, s2.initials, s2.lastName
FROM Student s1, Student s2
WHERE s1.studentId <> s2.studentID /* Every student has the same tutor as himself */
AND s1.tutorId = s2.tutorid

获取导师列表 - 学生:

SELECT tutorId, GROUP_CONCAT( initials, lastName SEPARATOR ', ') 
FROM `Student` 
GROUP BY tutorId
/* to only show tutors that have more than 1 student: */
/* HAVING COUNT(studentid) > 1 */