SQL Adventure Works Получение непосредственного менеджера

Как изменить следующую процедуру, чтобы получить непосредственный/непосредственный менеджер? Я не хочу получать всех менеджеров человека. Только непосредственный менеджер.

CREATE PROCEDURE [dbo].[uspGetEmployeeManagers]
 @EmployeeID [int]
AS
BEGIN
 SET NOCOUNT ON;

 -- Use recursive query to list out all Employees required for a particular Manager
 WITH [EMP_cte]([EmployeeID], [ManagerID], [FirstName], [LastName], [Title], [RecursionLevel]) -- CTE name and columns
 AS (
 SELECT e.[EmployeeID], e.[ManagerID], c.[FirstName], c.[LastName], e.[Title], 0 -- Get the initial Employee
 FROM [HumanResources].[Employee] e 
 INNER JOIN [Person].[Contact] c 
 ON e.[ContactID] = c.[ContactID]
 WHERE e.[EmployeeID] = @EmployeeID
 UNION ALL
 SELECT e.[EmployeeID], e.[ManagerID], c.[FirstName], c.[LastName], e.[Title], [RecursionLevel] + 1 -- Join recursive member to anchor
 FROM [HumanResources].[Employee] e 
 INNER JOIN [EMP_cte]
 ON e.[EmployeeID] = [EMP_cte].[ManagerID]
 INNER JOIN [Person].[Contact] c 
 ON e.[ContactID] = c.[ContactID]
 )
 -- Join back to Employee to return the manager name 
 SELECT [EMP_cte].[RecursionLevel], [EMP_cte].[EmployeeID], [EMP_cte].[FirstName], [EMP_cte].[LastName], 
 [EMP_cte].[ManagerID], c.[FirstName] AS 'ManagerFirstName', c.[LastName] AS 'ManagerLastName' -- Outer select from the CTE
 FROM [EMP_cte] 
 INNER JOIN [HumanResources].[Employee] e 
 ON [EMP_cte].[ManagerID] = e.[EmployeeID]
 INNER JOIN [Person].[Contact] c 
 ON e.[ContactID] = c.[ContactID]
 ORDER BY [RecursionLevel], [ManagerID], [EmployeeID]
 OPTION (MAXRECURSION 25) 
END;
2 ответа

Или заменить

-- Join back to Employee to return the manager name 
 SELECT [EMP_cte].[RecursionLevel], [EMP_cte].[EmployeeID], [EMP_cte].[FirstName], [EMP_cte].[LastName], 
 [EMP_cte].[ManagerID], c.[FirstName] AS 'ManagerFirstName', c.[LastName] AS 'ManagerLastName' -- Outer select from the CTE
 FROM [EMP_cte] 
 INNER JOIN [HumanResources].[Employee] e 
 ON [EMP_cte].[ManagerID] = e.[EmployeeID]
 INNER JOIN [Person].[Contact] c 
 ON e.[ContactID] = c.[ContactID]
 ORDER BY [RecursionLevel], [ManagerID], [EmployeeID]
 OPTION (MAXRECURSION 25)

с

-- Join back to Employee to return the manager name 
 SELECT **top 1** [EMP_cte].[RecursionLevel], [EMP_cte].[EmployeeID], [EMP_cte].[FirstName], [EMP_cte].[LastName], 
 [EMP_cte].[ManagerID], c.[FirstName] AS 'ManagerFirstName', c.[LastName] AS 'ManagerLastName' -- Outer select from the CTE
 FROM [EMP_cte] 
 INNER JOIN [HumanResources].[Employee] e 
 ON [EMP_cte].[ManagerID] = e.[EmployeeID]
 INNER JOIN [Person].[Contact] c 
 ON e.[ContactID] = c.[ContactID]
 ORDER BY [RecursionLevel], [ManagerID], [EmployeeID]
 OPTION (MAXRECURSION 25)


Пытаться

CREATE PROCEDURE [dbo].[uspGetEmployeeManagers]
 @EmployeeID [int]
AS
BEGIN
 SET NOCOUNT ON;
 SELECT 
 e.[EmployeeID], e.[ManagerID], c.[FirstName], c.[LastName], e.[Title],
 hr.[EmployeeID], hr.[ManagerID], chr.[FirstName], chr.[LastName], hr.[Title]
 FROM [HumanResources].[Employee] e
 -- get info of employee 
 INNER JOIN [Person].[Contact] c 
 ON e.[ContactID] = c.[ContactID]
 -- get the info of immediate manager 
 LEFT JOIN [HumanResources].[Employee] hr 
 ON e.[ManagerID] = hr.[EmployeeID]
 LEFT JOIN [Person].[Contact] chr 
 ON hr.[ContactID] = chr.[ContactID]
 WHERE e.[EmployeeID] = @EmployeeID
END;

licensed under cc by-sa 3.0 with attribution.