如何用HAVING子句仅显示对应最大课程完成数的单个ID？

你可以通过嵌套子查询在HAVING子句中动态获取最大课程数，避免硬写具体数值，对应的SQL语句如下：

select s.id, count(s.id_class) as max_cl
from classes as s 
    join teachers as t on s.id=t.id
group by s.id
having count(s.id_class) = (
    select max(cnt)
    from (
        select count(id_class) as cnt
        from classes
        group by id
    ) as sub_query
)

语句说明： #

• 最内层子查询 (select count(id_class) as cnt from classes group by id)：统计每个ID对应的课程数量，得到所有ID的课程数集合。
• 中间子查询 select max(cnt) from (...) as sub_query：从上述集合中提取出最大的课程数。
• 主查询的HAVING子句：将当前分组的课程数与动态获取的最大值做对比，仅保留课程数等于最大值的ID记录。

如果存在多个ID的课程数同时等于最大值，该语句会返回所有符合条件的ID；若仅需返回单个结果，可在主查询末尾添加对应数据库的限制语法，比如MySQL用limit 1，SQL Server用top 1。

内容的提问来源于stack exchange，提问作者slavabuu