SQL求助：如何筛选教授至少2门且平均分差≥1课程的教师？

首先，你的原查询问题在于COUNT(*)统计的是教师与课程开设的关联记录数，而非不同课程的数量。如果一位教师多次教授同一门课的不同班次，COUNT(*)会返回大于1的结果，但实际只教授了1门课程，导致筛选结果不准确。

1. 修正“教授至少2门课程”的查询 #

要统计教师教授的不同课程数量，需使用COUNT(DISTINCT co.courseid)：

SELECT t.teacherid, t.teachername
FROM teacherassignmentstocourseoffers ta 
JOIN teachers t ON t.teacherid = ta.teacherid 
JOIN courseoffers co ON co.courseofferid = ta.courseofferid
GROUP BY t.teacherid, t.teachername
HAVING COUNT(DISTINCT co.courseid) >= 2;

2. 完整需求：筛选满足平均分差值≥1的教师 #

要同时满足“至少2门课程”且“课程平均分差值≥1”，需要先计算每位教师每门课程的平均分，再基于这些平均分筛选符合条件的教师：

SELECT teacherid, teachername
FROM (
    -- 子查询：计算每位教师每门课程的平均分
    SELECT 
        t.teacherid,
        t.teachername,
        co.courseid,
        AVG(g.score) AS course_avg
    FROM teachers t
    JOIN teacherassignmentstocourseoffers ta 
        ON t.teacherid = ta.teacherid
    JOIN courseoffers co 
        ON ta.courseofferid = co.courseofferid
    JOIN grades g 
        ON co.courseofferid = g.courseofferid -- 关联成绩表获取分数
    GROUP BY t.teacherid, t.teachername, co.courseid
) AS teacher_course_avgs
GROUP BY teacherid, teachername
HAVING 
    COUNT(courseid) >= 2 -- 至少2门不同课程
    AND MAX(course_avg) - MIN(course_avg) >= 1; -- 最高分与最低分差值≥1

关键说明 #

• 假设数据库中存在grades表存储学生成绩，通过courseoffers关联到成绩数据；若你的成绩表命名不同，需替换为对应表名。
• 子查询先按教师和课程分组计算平均分，外层查询再按教师分组，通过MAX(course_avg)-MIN(course_avg)判断平均分差值是否符合要求。

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