如何使用SQL关联teachers与schools双表实现指定数据查询

已知表结构 #

• teachers 教师表存储教师基础信息：
  • id：int 类型，教师唯一标识
  • name：varchar 类型，教师姓名
  • school_id：int 类型，关联所属学校的外键字段
  • age：int 类型，教师年龄
• schools 学校表存储学校基础信息：
  • id：int 类型，学校唯一主键
  • name：varchar 类型，学校名称

需求1：输出所有教师姓名及其任教的学校名称 #

原编写SQL的错误点 #

原尝试语句：

SELECT Name.Teachers, Name.School
JOIN Schools S ON School.ID = S.ID
FROM Teachers T

存在三处核心问题：

• 语法顺序错误：SQL规范要求先通过FROM指定主查询表，再通过JOIN定义关联逻辑，最后通过SELECT指定返回字段，原语句将JOIN子句放在FROM之前，不符合语法执行规则
• 字段引用错误：表与字段的引用格式为表名/别名.字段名，原语句将字段名与表名嵌套颠倒，不存在Name.Teachers这类合法字段引用
• 关联逻辑错误：教师表与学校表的关联依赖教师表的school_id匹配学校表的主键id，原关联条件未使用该关联字段，无法建立两表的正确映射关系

修正后的可执行SQL #

SELECT
  T.name AS teacher_name,
  S.name AS school_name
FROM Teachers T
LEFT JOIN Schools S
  ON T.school_id = S.id;

注：使用LEFT JOIN可保留全量教师记录，即使存在未匹配到对应学校的异常教师数据；若仅需查询匹配到有效学校的教师记录，可将关联方式替换为INNER JOIN

需求2：输出各学校内每位教师与次年长教师的年龄差 #

实现思路 #

按学校ID维度对教师分组，组内教师按年龄从高到低排序，提取同组内年龄排第2位的教师年龄作为参照基准，计算每位教师年龄与该基准值的差值。

可执行SQL（兼容支持窗口函数的主流数据库） #

WITH ranked_teachers AS (
  SELECT
    S.name AS school_name,
    T.name AS teacher_name,
    T.age,
    NTH_VALUE(T.age, 2) OVER (
      PARTITION BY T.school_id
      ORDER BY T.age DESC
      -- 窗口范围覆盖整个分组，避免受默认行范围限制导致取值错误
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS second_oldest_age
  FROM Teachers T
  LEFT JOIN Schools S ON T.school_id = S.id
)
SELECT
  school_name,
  teacher_name,
  age - second_oldest_age AS age_gap_to_second_oldest
FROM ranked_teachers
-- 过滤教师数不足2人的学校，这类场景不存在有效的次年长教师参照值
WHERE second_oldest_age IS NOT NULL;

注：如果需要严格按排序位次取第2位教师（不处理同年龄并列场景），可在窗口计算中补充排名逻辑校验，避免并列排名导致的取值错位

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