SQL层次结构选择两个表之间的查询

Question

我有两个表(People, Department)。

部门表如下:

ID (int)
Name (varchar)
ParentID (int)

人表如下:

Id (int)
FirstName (varchar)
SureName (varchar)
DepartmentId (int)
Manager (Bool)
Asistant (Bool)

我需要创建查询，它选择具体人员的所有直接下属，具体人员是具体部门的经理。其中一个查询不仅选择了所有经理的直接下属。每个部门有一名经理和一名助理。具有manager == false的人员是直接下属，而在子部门的人员是父部门的下属。

我不知道如何在SQL/LINQ中创建这个查询。

我将感激任何帮助!

的例子:部门

Id   Name          Parent
0    Department1   null
1    Department2   0
2    Department3   1

的例子:

People
ID    Name      Department    DepartmentId    Manager    Asistant
1     Martin    Joshua            0             1           0
2     Ondra     Joshua2           0             0           0
3     Petr      Joshua3           0             0           0
4     Todd      Joshua3           1             1           0
5     Alex      Joshua3           1             0           0
6     Iva       Joshua3           1             0           0
7     Otto      Joshua3           2             1           0
8     Todd      Joshua3           2             0           0

例如，我需要选择id为0的部门经理的所有(不仅仅是直接)下属，结果将看起来像:

2     Ondra     Joshua2           0             0           0
3     Petr      Joshua3           0             0           0
4     Todd      Joshua3           1             1           0
5     Alex      Joshua3           1             0           0
6     Iva       Joshua3           1             0           0
7     Otto      Joshua3           2             1           0
8     Todd      Joshua3           2             0           0

I´m not sure直接下属SQL查询:

SELECT * FROM dbo.PeopleView WHERE DepartmentId = 162 AND Manager = 0; -- all direct s (162)

解决方案:

;WITH CTE AS
(
    SELECT 1 as EMPLEVEL, H1.Id, H1.ParentId, H1.Name FROM DepartmentView H1 WHERE Id = 6
    UNION ALL
    SELECT EMPLEVEL + 1, H2.Id, H2.ParentId, H2.Name FROM DepartmentView H2
    INNER JOIN CTE ON H2.ParentId = CTE.Id
)
SELECT DISTINCT P.Id, P.LastName,P.FirstName,P.DepartmentId,P.Manager,P.Assistant FROM CTE as T JOIN PeopleView as P on T.Id = P.DepartmentId;

Answer 1

除了您的第一个查询之外，就像@Andrew所说的评论一样，递归查询可以帮助解决这个问题:

;WITH CTE
AS
(
SELECT ID,FirstName,SureName,DepartmentID,Manager,Assistant, 0 AS EMPLEVEL  FROM PEOPLE A  WHERE DepartmentId = 1 AND Manager = 0
UNION ALL 
SELECT B.ID,B.FirstName,B.SureName,B.DepartmentID,B.Manager,B.Assistant,EMPLEVEL +1 FROM PEOPLE B 
INNER JOIN DEPARTMENT D
ON B.DepartmentID = D.ID
INNER JOIN CTE 
ON D.ParentID = CTE.DepartmentID 
)
SELECT DISTINCT * FROM CTE 

和转换为LINQ，你可以阅读这篇文章:通用表表达式(CTE)在linq-to-sql?

希望这对你有帮助，致以最诚挚的问候。