mysql - 选择 max(value) 和对应的行，而不使用 rownum 或 limit

Question

有一个包含加入日期的教师表，需要编写一个查询来查找大多数加入学校的教师，并且查询应显示当年加入的教师的年份和编号。
我已经编写了使用 Limit 得到结果的查询，但是还有其他方法来编写查询吗？

select
  count(teah_id) as no_of_teachers,
  substr(DATE_OF_JOINING,1,4) as year
from teacher
group by substr(DATE_OF_JOINING,1,4)
order by count(phy_id) desc
limit 1

Answer 1

您可以将当前查询放入 CTE 中，然后重复使用它来查找最高计数:

with cte as (
    select
        count(teah_id) as no_of_teachers,
        substr(DATE_OF_JOINING,1,4) as year
    from teacher
    group by substr(DATE_OF_JOINING, 1, 4)
)

select
    year,
    no_of_teachers
from cte
where
    no_of_teachers = (select max(no_of_teachers) from cte);