PostgreSQL查询：筛选满足跨课程成绩差条件的教师

数据集 #

需求 #

编写PostgreSQL查询语句，筛选出满足以下条件的教师：

• 教授至少两门不同课程
• 存在两门不同课程X和Y，使得课程Y的Maximum_grade与课程X的Minimum_grade的差值大于2（注：根据预期结果推导，需求应为该方向的差值；若为X的Minimum_grade减Y的Maximum_grade大于2，则无符合条件的教师）

预期结果仅选出Teacher_ID为4的教师。

尝试的SQL语句 #

SELECT Teacher_ID, MAX(Maximum_grade), MIN(Minimum_grade)
FROM dataset
GROUP BY Teacher_ID
HAVING count(Teacher_ID) > 1 AND (MAX(Maximum_grade) - Min(Minimum_grade)) > 2;

问题分析 #

该语句错误选出了Teacher_ID为3和4的教师，原因在于：

• 逻辑取的是教师所有课程中的全局最高分和全局最低分做差，但这两个分数可能来自同一门课程（比如教师3的全局最高分8和最低分5都来自课程4）
• 即使分数来自不同课程，也无法保证存在一对不同课程满足“Y的最高分 - X的最低分 >2”的条件（教师3的所有课程对差值最大为2，不满足>2）

改进方案 #

需要通过自连接或EXISTS子查询的方式，明确对比同一教师的不同课程之间的成绩关系，确保符合条件的分数来自两门不同课程。

方法1：自连接+去重 #

SELECT DISTINCT d1.Teacher_ID
FROM dataset d1
JOIN dataset d2 
  ON d1.Teacher_ID = d2.Teacher_ID 
  AND d1.Course_ID != d2.Course_ID -- 确保对比的是不同课程
WHERE d2.Maximum_grade - d1.Minimum_grade > 2;

方法2：使用EXISTS子查询 #

SELECT DISTINCT Teacher_ID
FROM dataset d_main
WHERE EXISTS (
    SELECT 1
    FROM dataset d_sub
    WHERE d_sub.Teacher_ID = d_main.Teacher_ID
      AND d_sub.Course_ID != d_main.Course_ID -- 排除同一课程
      AND d_sub.Maximum_grade - d_main.Minimum_grade > 2
);

结果说明 #

• 自连接和子查询方法都会精准筛选出Teacher_ID=4的教师：教师4的课程7（Minimum_grade=4）和课程8（Maximum_grade=7）满足7-4=3>2的条件
• 教师3的所有课程组合中，最大差值为2（如课程4的Maximum_grade=8与课程5的Minimum_grade=6，8-6=2），不满足>2的要求，因此不会被选中

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