如何用T-SQL按树形结构排序层级化员工数据？

要实现既按字母顺序排列，又让员工紧随直属经理之后的树形展示效果，核心是生成每个员工的完整层级排序路径，通过路径字段实现排序。以下是具体解决方案：

模拟数据 #

SELECT 'Bart Simpson' AS EmployeeName, 'Marge Simpson' AS ManagerName, 5 AS OrgLevel UNION
SELECT 'Bruce' AS EmployeeName, 'Mr Burns' AS ManagerName, 2 AS OrgLevel UNION
SELECT 'Donald' AS EmployeeName, 'Mr Burns' AS ManagerName, 2 AS OrgLevel UNION
SELECT 'Homer Simpson' AS EmployeeName, 'Bruce' AS ManagerName, 3 AS OrgLevel UNION
SELECT 'Lisa Simpson' AS EmployeeName, 'Marge Simpson' AS ManagerName, 5 AS OrgLevel UNION
SELECT 'Maggie Simpson' AS EmployeeName, 'Marge Simpson' AS ManagerName, 5 AS OrgLevel UNION
SELECT 'Marge Simpson' AS EmployeeName, 'Homer Simpson' AS ManagerName, 4 AS OrgLevel UNION
SELECT 'Mr Burns' AS EmployeeName, '' AS ManagerName, 1 AS OrgLevel UNION
SELECT 'The Cat' AS EmployeeName, 'Mr Burns' AS ManagerName, 2 AS OrgLevel

解决方案SQL #

WITH OrgHierarchy AS (
    -- 锚点：顶级经理
    SELECT 
        EmployeeName,
        ManagerName,
        OrgLevel,
        CAST(EmployeeName AS VARCHAR(MAX)) AS SortPath
    FROM 
        (
            SELECT 'Bart Simpson' AS EmployeeName, 'Marge Simpson' AS ManagerName, 5 AS OrgLevel UNION
            SELECT 'Bruce' AS EmployeeName, 'Mr Burns' AS ManagerName, 2 AS OrgLevel UNION
            SELECT 'Donald' AS EmployeeName, 'Mr Burns' AS ManagerName, 2 AS OrgLevel UNION
            SELECT 'Homer Simpson' AS EmployeeName, 'Bruce' AS ManagerName, 3 AS OrgLevel UNION
            SELECT 'Lisa Simpson' AS EmployeeName, 'Marge Simpson' AS ManagerName, 5 AS OrgLevel UNION
            SELECT 'Maggie Simpson' AS EmployeeName, 'Marge Simpson' AS ManagerName, 5 AS OrgLevel UNION
            SELECT 'Marge Simpson' AS EmployeeName, 'Homer Simpson' AS ManagerName, 4 AS OrgLevel UNION
            SELECT 'Mr Burns' AS EmployeeName, '' AS ManagerName, 1 AS OrgLevel UNION
            SELECT 'The Cat' AS EmployeeName, 'Mr Burns' AS ManagerName, 2 AS OrgLevel
        ) t
    WHERE ManagerName = ''

    UNION ALL

    -- 递归：关联下属，生成完整排序路径
    SELECT 
        t.EmployeeName,
        t.ManagerName,
        t.OrgLevel,
        CAST(oh.SortPath + '|' + t.EmployeeName AS VARCHAR(MAX)) AS SortPath
    FROM 
        (
            SELECT 'Bart Simpson' AS EmployeeName, 'Marge Simpson' AS ManagerName, 5 AS OrgLevel UNION
            SELECT 'Bruce' AS EmployeeName, 'Mr Burns' AS ManagerName, 2 AS OrgLevel UNION
            SELECT 'Donald' AS EmployeeName, 'Mr Burns' AS ManagerName, 2 AS OrgLevel UNION
            SELECT 'Homer Simpson' AS EmployeeName, 'Bruce' AS ManagerName, 3 AS OrgLevel UNION
            SELECT 'Lisa Simpson' AS EmployeeName, 'Marge Simpson' AS ManagerName, 5 AS OrgLevel UNION
            SELECT 'Maggie Simpson' AS EmployeeName, 'Marge Simpson' AS ManagerName, 5 AS OrgLevel UNION
            SELECT 'Marge Simpson' AS EmployeeName, 'Homer Simpson' AS ManagerName, 4 AS OrgLevel UNION
            SELECT 'Mr Burns' AS EmployeeName, '' AS ManagerName, 1 AS OrgLevel UNION
            SELECT 'The Cat' AS EmployeeName, 'Mr Burns' AS ManagerName, 2 AS OrgLevel
        ) t
    JOIN OrgHierarchy oh ON t.ManagerName = oh.EmployeeName
)
-- 按排序路径输出，添加缩进展示树形结构
SELECT 
    REPLICATE('  ', OrgLevel - 1) + EmployeeName AS DisplayName,
    OrgLevel,
    SortPath
FROM OrgHierarchy
ORDER BY SortPath;

效果说明 #

执行后会得到以下符合要求的树形排序结果（缩进体现层级）：

• Mr Burns
  • Bruce
    • Homer Simpson
      • Marge Simpson
        • Bart Simpson
        • Lisa Simpson
        • Maggie Simpson
  • Donald
  • The Cat

核心逻辑 #

1. 递归生成排序路径：通过CTE递归，为每个员工生成包含从顶级经理到自身的完整名称路径，路径用分隔符（如|）拼接。
2. 按路径排序：排序时直接使用SortPath，既保证经理在前、下属紧跟，又因为字符串默认按字母自然排序，同层级员工会自动按字母顺序排列。

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