检索员工，报告经理和高级报告经理 [英] Retrieving Employee,Reporting manager and senior reporting manager

问题描述

我必须找回员工，报告经理和高级报告经理

Hi,
I have to retrieve employee, reporting manager and senior reporting manager

select
		HR2.EmployeeName as Employee_Name,
		HR1.EmployeeName as Reporting_Manager,
		HR3.EmployeeName as Seniour_Reporting_Manager
from 
		HR HR1, HR HR2, HR HR3
where 
		HR1.EmployeeName= HR2.ReportingManagerName
	 and 
                HR1.ReportingManagerName = HR3.EmployeeName
order by 
		HR2.EmployeeName 

但它将数据恢复为

but it retriving the data as

Employee_Name|Reporting_Manager|Seniour_Reporting_Manager
-------------|-----------------|---------------------------
abc          |def              |ghi
scd          |asd              |qwe

价值来得正确



但是我需要报告资金进来同一栏

喜欢

the value is coming correctly

but i need the reporting mamger to come in same column
like

Employee_Name|Reporting_Manager
-------------|------------------
abc          |def              
abc          |ghi       

谢谢

thank you

推荐答案

如果我理解正确你有一个与递归查询 [ ^ ]。如果您点击该链接，您将找到使用完全相同模型的示例（employee-> manager）。



要使查询适应您的示例，结果将看起来像这样：

If I understood you correctly you have a homework assignment related to the recursive queries[^]. If you follow the link you will find the example that uses exactly the same model (employee->manager).

To adapt the query to your example the result will look something like this:

;WITH DirectReports (EmployeeName, EmployeeManager, Level)
AS
(
-- Anchor member definition
    SELECT e.EmployeeName, 0 AS Level
    FROM HR AS e
    WHERE e.ReportingManagerName IS NULL
    UNION ALL
-- Recursive member definition
    SELECT e.EmployeeName, 
        Level + 1
    FROM HR AS e
    INNER JOIN DirectReports AS d
        ON e.ReportingManagerName = e.EmployeeName
)
-- Statement that executes the CTE
SELECT EmployeeName, EmployeeManager, Level
FROM DirectReports
ORDER BY EmployeeName;

此查询解决了我的问题问题

This query solved my problem

declare  @Reporties table (EmployeeName varchar(50), Reporting_Manager varchar(50))

insert @Reporties (EmployeeName,Reporting_Manager)(
    SELECT H.EmployeeName, H.ReportingManagerName
    FROM HR AS H
    UNION ALL
   select HR2.EmployeeName,HR3.EmployeeName
from 
		HR HR1, HR HR2, HR HR3
where 
		HR1.EmployeeName= HR2.ReportingManagerName
	and HR1.ReportingManagerName = HR3.EmployeeName
)
SELECT distinct EmployeeName, Reporting_Manager
FROM @Reporties
ORDER BY EmployeeName

这篇关于检索员工，报告经理和高级报告经理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！