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;

- openshac

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;

- Gordon Linoff

---

查询似乎创建了一个“Adam//Brett”。我认为查询的第一部分需要将“userName”转换为“CONVERT(VARCHAR(MAX), userName) as path”。 - openshac

@openshac……我不认为这会影响顺序，但我把“/”移到了我真正想要的位置。 - Gordon Linoff

Answer 2

如何使用SQL Server hierarchyid来正确排序这些数据：SQL Fiddle MS SQL Server 2014架构设置：

CREATE TABLE [dbo].[Users](
    [userId] [int] ,
    [userName] [varchar](50) ,
    [managerId] [int] ,
   )

INSERT INTO dbo.Users
    ([userId], [userName], [managerId])
VALUES
(1,'Adam',NULL),
(2,'Brett',1),
(3,'Chris',2),
(4,'George',1),
(5,'David',3),
(6,'Elliot',5),
(7,'Frank',5),
(8,'Harry',4)

查询 1:

WITH UserCTE AS (
  SELECT userId, userName, managerId, hierarchyid::GetRoot() AS EmpLevel
  FROM Users where managerId is null

  UNION ALL

  SELECT usr.userId, usr.userName, usr.managerId
         , cast(mgr.EmpLevel.ToString() + cast(usr.userId As varchar(30)) + '/' as hierarchyid) as EmpLevel
  FROM Users AS usr
    INNER JOIN UserCTE AS mgr
      ON usr.managerId = mgr.userId where usr.managerId IS NOT NULL
)
SELECT * , EmpLevel.ToString()
  FROM UserCTE AS u 
  ORDER BY EmpLevel

Results:

| userId | userName | managerId | EmpLevel |           |
|--------|----------|-----------|----------|-----------|
|      1 |     Adam |    (null) |          |         / |
|      2 |    Brett |         1 |     aA== |       /2/ |
|      3 |    Chris |         2 |     a8A= |     /2/3/ |
|      5 |    David |         3 |     a+M= |   /2/3/5/ |
|      6 |   Elliot |         5 |     a+OU | /2/3/5/6/ |
|      7 |    Frank |         5 |     a+Oc | /2/3/5/7/ |
|      4 |   George |         1 |     hA== |       /4/ |
|      8 |    Harry |         4 |     hog= |     /4/8/ |

- Steve Ford

---

太好了，EmpLevel字段对我来说非常有用。 - openshac