如何从 SQL 表中检索分层数据

我有 2 个表：T_Employees 和 T_Projects

每个项目都分配了不同数量的员工。我需要做的是获取每个员工的层次结构，并将其分配给特定的项目。

请看下图和预期结果。

如何在 Microsoft SQL Server 2008R2 上完成此操作？

CREATE TABLE [dbo].[T_Projects](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ProjectId] [int] NOT NULL,
    [EmployeeId] [int] NOT NULL,
    CONSTRAINT [PK_T_Projects] PRIMARY KEY CLUSTERED([ID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY] 
GO

SET IDENTITY_INSERT [dbo].[T_Projects] ON
INSERT [dbo].[T_Projects] ([ID], [ProjectId], [EmployeeId]) VALUES (1, 456,10)
INSERT [dbo].[T_Projects] ([ID], [ProjectId], [EmployeeId]) VALUES (2, 456, 12)
INSERT [dbo].[T_Projects] ([ID], [ProjectId], [EmployeeId]) VALUES (3, 23, 11)
SET IDENTITY_INSERT [dbo].[T_Projects] OFF

CREATE TABLE [dbo].[T_Employees](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Level] [int] NOT NULL,
    [Employee] [nvarchar](256) NOT NULL,
    [Department] [nvarchar](5) NOT NULL,
    [MasterId] [int] NULL,
    [Code] [nvarchar](10) NOT NULL,
    [Note] [nvarchar](100) NULL,
    CONSTRAINT [PK_T_Employees] PRIMARY KEY CLUSTERED ([ID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]
GO

SET IDENTITY_INSERT [dbo].[T_Employees] ON
INSERT [dbo].[T_Employees] ([ID], [Level], [Employee], [Department], [MasterId], [Code], [Note]) VALUES (1, 1, N'Thomas S.', N'A', NULL, N'1-4', NULL)
INSERT [dbo].[T_Employees] ([ID], [Level], [Employee], [Department], [MasterId], [Code], [Note]) VALUES (2, 1, N'Michael F.', N'A', NULL, N'1-5', NULL)
INSERT [dbo].[T_Employees] ([ID], [Level], [Employee], [Department], [MasterId], [Code], [Note]) VALUES (3, 1, N'Simone S.', N'A', NULL, N'1-3', NULL)
INSERT [dbo].[T_Employees] ([ID], [Level], [Employee], [Department], [MasterId], [Code], [Note]) VALUES (4, 2, N'Stefan K.', N'B', 1, N'2-18', NULL)
INSERT [dbo].[T_Employees] ([ID], [Level], [Employee], [Department], [MasterId], [Code], [Note]) VALUES (5, 2, N'Mike T.', N'B', 2, N'2-96', NULL)
INSERT [dbo].[T_Employees] ([ID], [Level], [Employee], [Department], [MasterId], [Code], [Note]) VALUES (6, 2, N'Loris P.', N'B', 3, N'2-15', NULL)
INSERT [dbo].[T_Employees] ([ID], [Level], [Employee], [Department], [MasterId], [Code], [Note]) VALUES (7, 3, N'Lennon I.', N'B', 4, N'2-19', NULL)
INSERT [dbo].[T_Employees] ([ID], [Level], [Employee], [Department], [MasterId], [Code], [Note]) VALUES (8, 3, N'Kerim K.', N'C', 4, N'2-66', NULL)
INSERT [dbo].[T_Employees] ([ID], [Level], [Employee], [Department], [MasterId], [Code], [Note]) VALUES (9, 3, N'Ilmas Y.', N'C', 6, N'2-59', NULL)
INSERT [dbo].[T_Employees] ([ID], [Level], [Employee], [Department], [MasterId], [Code], [Note]) VALUES (10, 4, N'Innes Y.', N'D', 8, N'3-89', NULL)
INSERT [dbo].[T_Employees] ([ID], [Level], [Employee], [Department], [MasterId], [Code], [Note]) VALUES (11, 4, N'Andreas U.', N'E', 7, N'3-63', NULL)
INSERT [dbo].[T_Employees] ([ID], [Level], [Employee], [Department], [MasterId], [Code], [Note]) VALUES (12, 4, N'Fatih O.', N'I', 9, N'3-32', NULL)
SET IDENTITY_INSERT [dbo].[T_Employees] OFF

您可以使用递归 CTE 遍历层次结构

这是经典的自上而下的 CTE：

WITH EmployeesHierarchy AS
(
    SELECT ID,[Level],Employee,Department,Code,MasterId
    FROM T_Employees
    WHERE [Level]=1
    UNION ALL
    SELECT nextLevel.ID,nextLevel.[Level],nextLevel.Employee,nextLevel.Department,nextLevel.Code,nextLevel.MasterId
    FROM EmployeesHierarchy AS recCall
    INNER JOIN T_Employees AS nextLevel ON nextLevel.[Level]=recCall.[Level]+1 AND nextLevel.MasterId=recCall.ID
)
SELECT * FROM EmployeesHierarchy
ORDER BY [Level],MasterId
GO

现在反过来：我从项目中提到的员工开始，向上移动列表，直到不再有parentId。在 CTE 第一部分中获取的项目数据刚刚通过并显示在所有行中。

WITH EmployeesHierarchy AS
(
    SELECT p.ID AS p_ID,p.ProjectId,e.ID AS e_ID,[Level],e.Employee,e.Department,e.Code,e.MasterId
    FROM T_Projects AS p 
        INNER JOIN T_Employees AS e ON p.EmployeeId=e.ID
    UNION ALL
    SELECT recCall.p_ID,recCall.ProjectId,nextLevel.ID,nextLevel.[Level],nextLevel.Employee,nextLevel.Department,nextLevel.Code,nextLevel.MasterId
    FROM EmployeesHierarchy AS recCall
    INNER JOIN T_Employees AS nextLevel ON nextLevel.ID=recCall.MasterId
)
SELECT * FROM EmployeesHierarchy
--WHERE ProjectId=456
ORDER BY [Level]

结果

+------+-----------+------+-------+------------+------------+------+----------+
| p_ID | ProjectId | e_ID | Level | Employee   | Department | Code | MasterId |
+------+-----------+------+-------+------------+------------+------+----------+
| 3    | 23        | 1    | 1     | Thomas S.  | A          | 1-4  | NULL     |
+------+-----------+------+-------+------------+------------+------+----------+
| 2    | 456       | 3    | 1     | Simone S.  | A          | 1-3  | NULL     |
+------+-----------+------+-------+------------+------------+------+----------+
| 1    | 456       | 1    | 1     | Thomas S.  | A          | 1-4  | NULL     |
+------+-----------+------+-------+------------+------------+------+----------+
| 2    | 456       | 6    | 2     | Loris P.   | B          | 2-15 | 3        |
+------+-----------+------+-------+------------+------------+------+----------+
| 1    | 456       | 4    | 2     | Stefan K.  | B          | 2-18 | 1        |
+------+-----------+------+-------+------------+------------+------+----------+
| 3    | 23        | 4    | 2     | Stefan K.  | B          | 2-18 | 1        |
+------+-----------+------+-------+------------+------------+------+----------+
| 3    | 23        | 7    | 3     | Lennon I.  | B          | 2-19 | 4        |
+------+-----------+------+-------+------------+------------+------+----------+
| 1    | 456       | 8    | 3     | Kerim K.   | C          | 2-66 | 4        |
+------+-----------+------+-------+------------+------------+------+----------+
| 2    | 456       | 9    | 3     | Ilmas Y.   | C          | 2-59 | 6        |
+------+-----------+------+-------+------------+------------+------+----------+
| 1    | 456       | 10   | 4     | Innes Y.   | D          | 3-89 | 8        |
+------+-----------+------+-------+------------+------------+------+----------+
| 2    | 456       | 12   | 4     | Fatih O.   | I          | 3-32 | 9        |
+------+-----------+------+-------+------------+------------+------+----------+
| 3    | 23        | 11   | 4     | Andreas U. | E          | 3-63 | 7        |
+------+-----------+------+-------+------------+------------+------+----------+