如何编写SQL查询筛选出教授超1门数学课且总课程数少于3的教师

数据库结构 #

Professor
    prof_id number
    name string
    salary number
    building string
Course
    name string
    prof_id number
    room_number number
    start_time number
    end_time number
Room
    room_number number
    capacity number
    building string

现有查询 #

查询至少教授1门数学课的教授 #

select distinct Professor.name, count(Course.name)  AS numberOfMathCourses
from Course
LEFT JOIN Room
ON Course.Room_id = Room.Room.id
INNER JOIN Professor
ON Professor.id = Course.id
where Course.name = 'math' 
group by Professor.name
having numberOfMathCourses > 0

查询总课程数少于3门的教授 #

select distinct Professor.name, count(Course.name)  AS numberOfCourses
from Course
LEFT JOIN Room
ON Course.Room_id = Room.Room.id
INNER JOIN Professor
ON Professor.id = Course.id
group by Professor.name
having numberOfCourses < 3

正确的SQL查询 #

要同时满足教授超过1门数学课和总课程数少于3门，可以通过一次分组+条件聚合实现，同时修正之前查询里的连接错误：

SELECT 
    p.name,
    COUNT(*) AS total_courses,
    SUM(CASE WHEN c.name = 'math' THEN 1 ELSE 0 END) AS math_courses
FROM Professor p
INNER JOIN Course c ON p.prof_id = c.prof_id
GROUP BY p.prof_id, p.name
HAVING SUM(CASE WHEN c.name = 'math' THEN 1 ELSE 0 END) > 1 
   AND COUNT(*) < 3;

关键修正与说明 #

1. 修正连接条件：数据库中教授和课程的关联字段是prof_id，不是id，所以用p.prof_id = c.prof_id关联两张表。
2. 移除冗余表连接：需求不需要Room表的信息，直接去掉LEFT JOIN Room可以提升查询效率。
3. 条件聚合统计：用SUM(CASE...)精准统计数学课数量，COUNT(*)统计教授的总课程数，一次分组就能获取两个核心指标。
4. 避免重名冲突：GROUP BY同时包含p.prof_id和p.name，防止同名教授被错误合并。
5. HAVING直接过滤：在分组后同时校验两个条件，无需拆分多次查询或子查询。

如果确实需要保留Room表连接（比如有未说明的关联需求），可调整为：

SELECT 
    p.name,
    COUNT(c.name) AS total_courses,
    SUM(CASE WHEN c.name = 'math' THEN 1 ELSE 0 END) AS math_courses
FROM Professor p
INNER JOIN Course c ON p.prof_id = c.prof_id
LEFT JOIN Room r ON c.room_number = r.room_number
GROUP BY p.prof_id, p.name
HAVING SUM(CASE WHEN c.name = 'math' THEN 1 ELSE 0 END) > 1 
   AND COUNT(c.name) < 3;

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