递归与公用表表达式 - 何时使用 CTE

Question

鉴于下表模式，有人问我如何找到安妮的所有下属。

-----------------------------
EmployeeId | Name | ManagerId
-----------------------------
1          | Annie| NULL
2          | Ben  | 1
3          | Carl | 2
4          | Den  | 1
....

来自ORM和服务器端背景，我会写一个递归函数来解决这个问题。但是，有人告诉我使用CTE有不同的方法。为什么以及何时使用 CTE ？为什么不递归？CTE 比递归快吗？

无论如何，这是我的递归实现：

public class Employee{
    int employeeId;
    string name;
    public List<Employee> managers {get;set};
    public List<Employee> subordinates {get;set};
}
//find direct & non-direct reports
public List<Employee> getSubordinates(Employee emp) {
    List<Employee> reports = new List<Employee>();
    if (emp.subordinates == null || emp.subordinates.Count == 0) 
       return null;
    foreach(Employee e in emp.subordinates) {
       reports.AddRange(getSubordinates(e));
    }
    return reports.DistinctBy(x=>x.employeeId);
}

Answer 1

公用表表达式 （CTE） 将帮助您从数据库端完成递归，因此实际上您将完成与方法相同的事情，但关键的例外是它是通过 SQL 通过一个查询完成的，例如：

WITH subordinates AS (
    SELECT e.EmployeeId, e.Name
    FROM dbo.Employee e
    WHERE e.ManagerId = 1
    UNION ALL
    SELECT e.EmployeeId, e.Name
    FROM dbo.Employee e
        INNER JOIN subordinates s ON s.EmployeeId = e.ManagerId
)
SELECT s.EmployeeId, s.Name
FROM subordinates s;

好处是它可能更快，因为它是在一次查询中完成的，而不是从递归 ORM 方法生成的多个查询。

T-SQL 小提琴示例