SQL Server单SQL查询多层师生层级：指定学生的顶级教师及关联师生

假设我们的师生关系表结构如下（一个学生仅对应一位教师，教师也可能是其他教师的学生，无层级限制）：

CREATE TABLE TeacherStudent (
    StudentID VARCHAR(50) NOT NULL PRIMARY KEY, -- 学生ID唯一，确保一个学生只关联一位教师
    TeacherID VARCHAR(50) NULL, -- 教师ID，NULL表示该用户是顶级教师（无上级）
    RelationDesc NVARCHAR(200) NULL -- 可选：师生关系的描述内容
);

单条SQL实现需求 #

使用**递归CTE（公共表表达式）**可以一次性获取目标学生的顶级教师，以及所有关联的师生关系链路。以下是两种输出形式：

形式1：聚合输出（顶级教师+拼接的全链路关系）

替换SQL中的'S001'为你要查询的学生ID即可：

WITH RecursiveTeacherStudent AS (
    -- 锚点：从目标学生开始
    SELECT 
        StudentID,
        TeacherID,
        RelationDesc,
        1 AS 层级,
        StudentID AS 顶级教师临时值
    FROM TeacherStudent
    WHERE StudentID = 'S001'

    UNION ALL

    -- 递归：向上追溯教师的上级
    SELECT 
        ts.StudentID,
        ts.TeacherID,
        ts.RelationDesc,
        rts.层级 + 1,
        -- 若当前节点是顶级（无教师），则它就是顶级教师，否则沿用之前的临时值
        CASE WHEN ts.TeacherID IS NULL THEN ts.StudentID ELSE rts.顶级教师临时值 END
    FROM TeacherStudent ts
    INNER JOIN RecursiveTeacherStudent rts ON ts.StudentID = rts.TeacherID
)
SELECT 
    MAX(顶级教师临时值) AS 顶级教师,
    -- 拼接所有师生关系，用换行分隔
    STRING_AGG(CONCAT(StudentID, ' -> ', TeacherID, ' (', ISNULL(RelationDesc, '无描述'), ')'), CHAR(10)) AS 全链路师生关系
FROM RecursiveTeacherStudent;

形式2：明细输出（每条关系带顶级教师）

如果需要查看每条师生关系的详细信息，同时附带顶级教师：

WITH RecursiveTeacherStudent AS (
    SELECT 
        StudentID,
        TeacherID,
        RelationDesc,
        1 AS 层级,
        StudentID AS 顶级教师
    FROM TeacherStudent
    WHERE StudentID = 'S001'

    UNION ALL

    SELECT 
        ts.StudentID,
        ts.TeacherID,
        ts.RelationDesc,
        rts.层级 + 1,
        CASE WHEN ts.TeacherID IS NULL THEN ts.StudentID ELSE rts.顶级教师 END
    FROM TeacherStudent ts
    INNER JOIN RecursiveTeacherStudent rts ON ts.StudentID = rts.TeacherID
)
SELECT 
    DISTINCT 顶级教师,
    StudentID AS 学生ID,
    TeacherID AS 教师ID,
    RelationDesc AS 关系描述,
    层级
FROM RecursiveTeacherStudent
ORDER BY 层级 DESC; -- 从顶级教师往下排序

逻辑说明 #

1. 递归CTE的锚点成员定位到目标学生，作为追溯的起点
2. 递归成员不断关联当前节点的教师（即把教师当作新的学生继续向上查），直到找到TeacherID为NULL的顶级教师
3. 通过CASE表达式在递归过程中传递顶级教师的ID，最终通过聚合或去重得到唯一的顶级教师
4. 层级字段可以帮你清晰看到链路的上下关系

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