如何使用Presto SQL递归查询找出最终上级非ID7的教师

需求说明 #

需要编写Presto SQL递归查询，遍历教师的汇报层级链（支持任意深度，包括循环汇报场景），筛选出最终汇报的顶级教师teacher_id不为7的所有教师，返回教师全名和teacher_id。

Presto SQL实现代码 #

WITH RECURSIVE teacher_hierarchy AS (
    -- 锚点成员：初始化每个教师的顶级上级为自身，记录访问路径避免循环
    SELECT
        teacher_id,
        教师全名,
        reporter_teacher,
        teacher_id AS top_teacher_id,
        ARRAY[teacher_id] AS visited_ids
    FROM teachers
    UNION ALL
    -- 递归成员：向上遍历汇报链，更新顶级上级，跳过已访问节点防止无限递归
    SELECT
        th.teacher_id,
        th.教师全名,
        t.reporter_teacher,
        CASE WHEN t.reporter_teacher IS NULL THEN t.teacher_id ELSE th.top_teacher_id END AS top_teacher_id,
        array_append(th.visited_ids, t.teacher_id) AS visited_ids
    FROM teacher_hierarchy th
    JOIN teachers t ON th.reporter_teacher = t.teacher_id
    WHERE NOT array_contains(th.visited_ids, t.teacher_id) -- 避免循环递归
)
-- 筛选顶级上级不是7的教师，去重后返回结果
SELECT DISTINCT
    教师全名,
    teacher_id
FROM teacher_hierarchy
WHERE top_teacher_id != 7
ORDER BY teacher_id;

代码解释 #

1. 递归CTE结构：使用WITH RECURSIVE定义递归查询，分为锚点和递归两部分：
   • 锚点成员：从teachers表中获取所有教师，初始时将每个教师自身标记为顶级上级，并用数组visited_ids记录已访问的节点ID，防止后续递归出现循环。
   • 递归成员：将当前层级的reporter_teacher与教师表关联，向上遍历汇报链；如果遇到无上级的教师（reporter_teacher为NULL），则将该教师设为顶级上级；同时通过array_contains检查是否已访问过该节点，避免无限递归。
2. 结果筛选：最终从递归结果中筛选出top_teacher_id不等于7的记录，通过DISTINCT去重（循环节点会被多次遍历），并按teacher_id排序。

测试数据（可用于验证） #

如果需要创建测试表，可执行以下语句：

CREATE TABLE teachers (
    教师全名 VARCHAR,
    teacher_id INT,
    reporter_teacher INT
);

INSERT INTO teachers VALUES
('Margo Holmes', 1, 2),
('Carry Miles', 2, 3),
('Philipp Baskov', 3, 4),
('Harry Potter', 4, 5),
('Daniel Lopez', 5, 6),
('Ivan Petrov', 6, 7),
('Jhon Doe', 7, NULL),
('Jane Ortego', 8, 9),
('Michael Powers', 9, 10),
('Sasha Li', 10, 9),
('Diana Norris', 11, 10),
('Wolter White', 12, 6);

预期输出 #

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