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

小编典典

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

sql

我有一个带有以下两个表的MySQL数据库：

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

返回共享同一家教师的任何学生的姓名缩写和姓氏的查询是什么？

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

阅读 156

2021-04-22

共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 */

2021-04-22