如何从SQL表中检索层次结构数据 [英] How to retrieve hierarchical data from SQL table

问题描述

我有2个表:T_Employees和T_Projects

I have 2 tables : T_Employees and T_Projects

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

Every project has different number of employees assigned. What i need to do, is to get hierarchical structure of each employee, assigned to a specific project.

看下面的图片和预期的结果.

Look at the image below and the expected results.

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

How to get this done on 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遍历层次结构

You can travers a hierarchy with a recursive CTE

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

This is the classical top down 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的第一部分中获得的项目数据将直接传递到所有行中.

And now the other way round: I start with the employees mentioned in the project and move up the list until there is no parentId any more. The Project's data gotten in the first part of the CTE are just passed through to show up in all rows.

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        |
+------+-----------+------+-------+------------+------------+------+----------+

这篇关于如何从SQL表中检索层次结构数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！