mysql - Mysql Max查询

我有两个表：

teachers (teacher_id,teacher_name)
courses (teacher_id,course_id)

我需要显示老师的姓名，教授最多课程数：

mysql>从以下位置选择Teachers.teacher_name，tmp1.teacher_id，tmp1.cnt（选择max（tm
p.cnt）作为tmpMax，来自（课程g中的选择Teacher_id，count（teacher_id）作为cnt
由Teacher_id路由）作为tmp）作为tmp2，（选择Teacher_id，count（teacher_id）作为cnt
（由Teacher_id按课程分组）作为tmp1，教师，其中tmp1.cnt = tmp2.tmpMax
和教师.teacher_id = tmp1.teacher_id;

我想出了上述查询。是否有针对此问题的更简单查询？

最佳答案

这应该工作：

select teacher_name
from teachers
where teacher_id IN
(
    select t.teacher_id
    from teachers t inner join courses c on t.teacher_id = c.teacher_id
    group by t.teacher_id
    having count(*) =
    (
        select max(courses_per_teacher) from
        (
            select teacher_id, count(*) as courses_per_teacher
            from teachers t inner join courses c on t.teacher_id = c.teacher_id
            group by teacher_id
        )
    )
)

伪代码中的逻辑：


查找ID在->中的教师姓名
课程数为->的教师组
等于->的最大数量
每位老师的课程数量列表


希望能有所帮助。