Presto SQL是否支持类SQL Server的CTE递归查询？含员工层级需求

Hey there! Let's tackle your questions about recursive CTEs in Presto step by step.

Does Presto support recursive CTEs like SQL Server? #

Absolutely! Presto does support recursive CTEs, but there's a key syntax difference from SQL Server: you need to explicitly use the RECURSIVE keyword right after WITH. SQL Server allows omitting it for recursive CTEs, but Presto requires it to recognize that the CTE is meant to reference itself.

Fixing your "Table cte does not exist" error #

The error you're seeing happens because you didn't use WITH RECURSIVE—Presto doesn't know that your CTE is supposed to reference itself, so it treats cte as a regular table that doesn't exist.

Here's the corrected version of your test query:

WITH RECURSIVE cte AS (
    SELECT 1 AS n
    UNION ALL
    SELECT cte.n + 1 FROM cte WHERE n < 50
)
SELECT * FROM cte;

This will generate numbers from 1 to 50 as expected.

Example: Employee hierarchy query #

For your actual use case of querying employee hierarchies, here's a practical example. Let's assume you have an employees table with columns employee_id, name, and manager_id (where manager_id references another employee's employee_id, or is NULL for top-level managers):

WITH RECURSIVE employee_hierarchy AS (
    -- Anchor member: Get top-level employees (no manager)
    SELECT
        employee_id,
        name,
        manager_id,
        1 AS hierarchy_level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive member: Join with the CTE to get subordinates
    SELECT
        e.employee_id,
        e.name,
        e.manager_id,
        eh.hierarchy_level + 1 AS hierarchy_level
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy
ORDER BY hierarchy_level, employee_id;

This query will return all employees along with their position in the hierarchy (level 1 for top managers, level 2 for their direct reports, etc.).

Key notes for Presto recursive CTEs #

• Always start with WITH RECURSIVE for recursive queries
• Split your CTE into two parts: the anchor member (initial non-recursive query) and the recursive member (the part that references the CTE itself), connected by UNION ALL
• Make sure your recursive member has a clear termination condition (like n < 50 in your test query, or the join that only pulls subordinates who have a manager in the CTE) to prevent infinite loops

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