mysql - SQL 计数/查询帮助

Question

所以我正在学习 MySQL，并且正在尝试执行以下操作:

对于每位讲师，请列出他/她的姓名和他/她指导的学生人数。

架构的相关部分是:

Person(ID, Name)
Student(StudentID, MentorID)
Instructor(InstructorID)

InstructorID 和StudentID 都映射到Person.ID，MentorID 映射到InstructorID (每个学生都有一个导师mentor，导师和学生都是Persons)。

我试过以下方法都无济于事:

select p.Name, count(select s.StudentID
                     from Student s
                     where s.MentorID = i.InstructorID)
from Person p, Instructor i
where p.ID = i.InstructorID;

也是在阅读 StackOverflow 上的一些内容之后:

select InstructorDetails.Name, count(Mentees)
from Instructor i
inner join Person as InstructorDetails
    on InstructorDetails.ID = i.InstructorID
inner join Student as Mentees
    on Mentees.MentorID = i.InstructorID;

有什么建议吗？

Answer 1

您的查询缺少GROUP BY，

SELECT InstructorDetails.Name, count(*) totalCount
FROM   Instructor i
       INNER JOIN Person as InstructorDetails
           ON InstructorDetails.ID = i.InstructorID
       INNER JOIN Student as Mentees
           ON Mentees.MentorID = i.InstructorID
GROUP BY InstructorDetails.Name