sqlserver 生成树
时间: 2023-12-20 15:06:33 浏览: 35
以下是使用公用表表达式(CTE)在SQL Server中生成树的示例:
```sql
WITH EmployeeHierarchy (EmployeeID, ManagerID, EmployeeName, HierarchyLevel) AS
(
-- 初始查询,获取根节点
SELECT EmployeeID, ManagerID, EmployeeName, 0 AS HierarchyLevel
FROM Employee
WHERE ManagerID IS NULL
UNION ALL
-- 递归查询,获取子节点
SELECT e.EmployeeID, e.ManagerID, e.EmployeeName, eh.HierarchyLevel + 1 AS HierarchyLevel
FROM Employee e
INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT EmployeeID, ManagerID, EmployeeName, HierarchyLevel
FROM EmployeeHierarchy
ORDER BY HierarchyLevel, EmployeeName;
```
上述示例中,我们使用了一个名为EmployeeHierarchy的公用表表达式来生成树形结构。在初始查询中,我们获取了根节点,即ManagerID为空的员工。然后,我们使用递归查询来获取每个节点的子节点,直到没有更多的子节点为止。最后,我们从EmployeeHierarchy中选择所有的节点,并按照层级和名称进行排序。