员工经理报表 - Sql Server [英] Employee Manager Report - Sql Server

问题描述

我有下表

empid   empname     managerID
1          A          NULL
2          B           1
3          C           1
4          D           2

ddl 如下

Declare @t table(empid int, empname varchar(20), managerID int)
insert into @t 

select 1,'A',null union all
select 2,'B',1 union all
select 3,'C',1 union all 
select 4,'D',2

我要做的是准备一份报告，指明哪些员工向哪个经理报告.

What I have to do is to prepare a report which will indicate which employees report to which manager.

我已经使用

select EmpName = e.empname, ManagerName = m.empname
from @t e 
left join
@t m
on  e.managerID = m.empid  

和所需的输出是

EmpName    ManagerName
A          NULL
B          A
C          A
D          B

还有哪些其他方法可以做到这一点?

What are the other ways of doing so?

推荐答案

Declare @t table(empid int, empname varchar(20), managerID int)

insert into @t 
select 1,'A',null union all
select 2,'B',1 union all
select 3,'C',1 union all 
select 4,'D',2

;with CTE AS
(
    Select empid,empname,managerID,
    Cast(empname As Varchar(max)) As ReportingManager
    from @T
    where managerID is null

    UNION ALL

    Select T.empid,T.empname,T.managerID,
    Cast(CTE.empname+'->'+t.empname As Varchar(max)) As ReportingManager
    from @T As T
    INNER JOIN CTE ON T.managerID=CTE.empid 
)
SELECT *
FROM CTE

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