sql - CTE递归有序树

Question

我已经创建了这个 SQL Fiddle具有以下数据

userId    userName    managerId
======    ========    =========
1         Adam        NULL
2         Brett       1
3         Chris       2
4         George      1
5         David       3
6         Elliot      5
7         Fred       5
8         Harry       4

如何返回一棵树，以便按以下顺序返回数据:

Adam
  Brett
    Chris
      David
        Elliot
        Fred
  George
    Harry

我不担心缩进，当然我不能只按名字排序(以防 Fred 更正为 Alfred)。

这是我目前所得到的:

WITH UserCTE AS (
  SELECT userId, userName, managerId, 0 AS EmpLevel
  FROM Users where managerId is null

  UNION ALL

  SELECT usr.userId, usr.userName, usr.managerId, mgr.[EmpLevel]+1
  FROM Users AS usr
    INNER JOIN UserCTE AS mgr
      ON usr.managerId = mgr.userId where usr.managerId IS NOT NULL
)
SELECT * 
  FROM UserCTE AS u 
  ORDER BY EmpLevel;

Answer 1

您需要获取每个人的完整路径，然后按此排序:

WITH UserCTE AS (
      SELECT userId, userName, managerId, 0 AS EmpLevel,
             CONVERT(VARCHAR(MAX), '/' + userName) as path
      FROM Users 
      WHERE managerId is null
      UNION ALL
      SELECT usr.userId, usr.userName, usr.managerId, mgr.[EmpLevel]+1,
             CONVERT(VARCHAR(MAX), mgr.path + '/' + usr.userName)
      FROM Users usr INNER JOIN
           UserCTE mgr
           ON usr.managerId = mgr.userId 
      WHERE usr.managerId IS NOT NULL  -- this is unnecessary
     )
SELECT * 
FROM UserCTE AS u 
ORDER BY path;