SQL Server中模拟Oracle的CONNECT BY PRIOR

在SQL Server中，模拟Oracle的CONNECT BY PRIOR功能是一项非常有用的技术。CONNECT BY PRIOR是一种递归查询语句，在Oracle数据库中常用于处理层次结构数据。然而，在SQL Server中并没有直接的等价语法。因此，我们需要使用一些技巧来模拟这个功能。

sql
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    manager_id INT
);
INSERT INTO employees (employee_id, employee_name, manager_id)
VALUES (1, 'John', NULL);
INSERT INTO employees (employee_id, employee_name, manager_id)
VALUES (2, 'Jane', 1);
INSERT INTO employees (employee_id, employee_name, manager_id)
VALUES (3, 'Tom', 1);
INSERT INTO employees (employee_id, employee_name, manager_id)
VALUES (4, 'Alice', 2);
INSERT INTO employees (employee_id, employee_name, manager_id)
VALUES (5, 'Bob', 2);
INSERT INTO employees (employee_id, employee_name, manager_id)
VALUES (6, 'Kate', 3);
INSERT INTO employees (employee_id, employee_name, manager_id)
VALUES (7, 'Mike', 3);

sql
WITH recursive_cte AS (
    SELECT employee_id, employee_name, manager_id, 0 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.employee_name, e.manager_id, level + 1
    FROM employees e
    INNER JOIN recursive_cte r ON e.manager_id = r.employee_id
)
SELECT employee_name, level
FROM recursive_cte
ORDER BY level, employee_id;