我有用户列表。每个用户都有一个由ParentId字段定义的层次结构(很少有用户位于层次结构的顶部-在此字段中为null)。我不想更改此表的结构(并向表中添加ExeplearchitectureId)。
目前，我有这张桌子:
使用者:
UserId INT NOT NULL, ManagerId INT NULL, other fields
我需要以表格的形式创建所有关系用户祖先和这些用户之间的级别差异的列表:
UserId, AncestorId, LevelDifference
例:
从用户表中:
UserId INT非空，ManagerId INT空
1，NULL，(吉姆)
2,1(乔什)
3,2(珍妮)
我应该得到:
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