mysql - 选择具有最大值的行(值由连接生成)

Question

我有一个数据库，可以这样总结:

teacher (tid, f_name, l_name);
subject (sid, title);
teacher_subject (tid, sid);

我想要的是让教授最多科目的老师，我在这里看到了一些类似但不重复的问题，无法修补解决方案以获得我想要的，简而言之，这就是我所拥有的写的:

select max(num_subs) from
   (select t.f_name, t.l_name, count(t.tid) num_subs
    from teacher t
    join teacher_subject ts
    on t.tid = ts.tid
    group by t.tid)
max_subs;

但不能再进一步了。我确信有办法实现它，因为我有时离它太近但从未到达。

Answer 1

由于缺少窗口函数或允许连接的限制子句，这在 MySQL 中有点尴尬，但现在开始:

select *
from teacher 
where tid in
(
  select tid
  from teacher_subject
  group by tid
  having count(*) =
  (
    select count(*)
    from teacher_subject
    group by tid
    order by count(*) desc
    limit 1
  )
);

---

仅作记录，在标准 SQL 中，这仅仅是:

select *
from teacher t
order by (select count(*) from teacher_subject ts where ts.tid = t.tid) desc
fetch first 1 row with ties;