我已经使用以下数据创建了这个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;