我有一个用户列表。每个用户都有一个ParentId字段定义的层级结构（其中一些用户处于层次结构的顶部 - 它们在此字段中具有空值）。我不想更改此表的结构（例如，向表中添加hierarchyId）。
目前我有这个表：
用户：
UserId INT NOT NULL, ManagerId INT NULL, other fields
UserId, AncestorId, LevelDifference
例子:
从用户表中:
UserId INT NOT NULL, ManagerId INT NULL
1,NULL, (Jim)
2,1 (Josh)
3,2 (Jenny)
我应该得到:
UserId, AncestorId, LevelDifference
2,1,1
3,2,1
3,1,2 - (Jim是Jenny的祖先之一)
有人有快速完成这个任务的想法吗？
更新 - 这应该是你要找的内容。 按照Joe Stefanelli所说，可以使用递归CTE：
表结构：
CREATE TABLE [HR].[Employees](
[empid] [int] IDENTITY(1,1) NOT NULL,
[lastname] [nvarchar](20) NOT NULL,
[firstname] [nvarchar](10) NOT NULL,
[mgrid] [int] NULL
);
我使用的样例数据：
empid lastname firstname mgrid
----------- -------------------- ---------- -----------
1 Davis Sara NULL
2 Funk Don 1
3 Lew Judy 2
4 Peled Yael 3
5 Buck Sven 2
6 Suurs Paul 5
7 King Russell 5
8 Cameron Maria 3
9 Dolgopyatova Zoya 5
查询：
WITH RCTE AS (
SELECT NULL AS PrevEmpId,
NULL AS PrevMgrId,
E.empid AS CurEmpId,
E.mgrid AS CurMgrid,
0 AS [Level],
E.lastname AS LastName,
E.firstname AS FirstName
FROM HR.Employees AS E
WHERE E.mgrid IS NULL
UNION ALL
SELECT PREV.CurEmpId AS PrevEmpId,
PREV.CurMgrid AS PrevMgrId,
CUR.empid AS CurEmpId,
CUR.mgrid AS CurMgrId,
Prev.Level + 1 AS [Level],
CUR.lastname AS LastName,
CUR.firstname AS FirstName
FROM RCTE AS PREV
JOIN HR.Employees AS CUR ON CUR.mgrid = PREV.CurEmpId
),RAnecestors AS (
SELECT E.empid AS StartEmpId,
NULL AS PrevEmpId,
NULL AS PrevMgrId,
E.empid AS CurEmpId,
E.mgrid AS CurMgrid,
1 AS [LevelDiff],
E.lastname AS LastName,
E.firstname AS FirstName
FROM HR.Employees AS E
UNION ALL
SELECT PREV.StartEmpId AS StartEmpId,
PREV.CurEmpId AS PrevEmpId,
PREV.CurMgrid AS PrevMgrId,
CUR.empid AS CurEmpId,
CUR.mgrid AS CurMgrId,
Prev.[LevelDiff] + 1 AS [LevelDiff],
CUR.lastname AS LastName,
CUR.firstname AS FirstName
FROM RAnecestors AS PREV
JOIN HR.Employees AS CUR ON CUR.empid = PREV.CurMgrid
)
SELECT RCTE.CurEmpId AS CurrentID,
RCTE.LastName AS CurrentLastName,
RAnecestors.CurEmpId AS AncestorID,
RAnecestors.LastName AS AncestorLastName,
[Level] AS [Level],
[LevelDiff] - 1 AS [LevelDiff]
LEFT JOIN RAnecestors ON RAnecestors.StartEmpId = RCTE.CurEmpId
AND RCTE.CurEmpId <> RAnecestors.CurEmpId
ORDER BY RCTE.CurEmpId, RAnecestors.LevelDiff
输出：
CurrentID CurrentLastName AncestorID AncestorLastName Level LevelDiff
----------- -------------------- ----------- -------------------- ----------- -----------
1 Davis NULL NULL 0 NULL
2 Funk 1 Davis 1 1
3 Lew 2 Funk 2 1
3 Lew 1 Davis 2 2
4 Peled 3 Lew 3 1
4 Peled 2 Funk 3 2
4 Peled 1 Davis 3 3
5 Buck 2 Funk 2 1
5 Buck 1 Davis 2 2
6 Suurs 5 Buck 3 1
6 Suurs 2 Funk 3 2
6 Suurs 1 Davis 3 3
7 King 5 Buck 3 1
7 King 2 Funk 3 2
7 King 1 Davis 3 3
8 Cameron 3 Lew 3 1
8 Cameron 2 Funk 3 2
8 Cameron 1 Davis 3 3
9 Dolgopyatova 5 Buck 3 1
9 Dolgopyatova 2 Funk 3 2
9 Dolgopyatova 1 Davis 3 3
我不会在SQL中这样做。只使用SQL就可以轻松获取具有祖先的用户列表，但是我不确定如何在没有tree结构的情况下计算级别差异。我并不是说你不能用SQL做到这一点，我只是不知道解决方案。
我会将您的用户放入树形数据结构中。从那里，获取级别差异（子树的高度）将更容易。