Employee Manager Report - Sql Server

问题:

I have the below table

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

The ddl is as under

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.

I have soved it using

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

and the desired output being

EmpName    ManagerName
A          NULL
B          A
C          A
D          B

What are the other ways of doing so?

回答1:

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

回答2:

I think, your solution is most appropriate, but we can rewrite it, e.g.:

select t1.empname [EmpName]
    , (select t2.empname from @t t2 where t2.empid = t1.managerID) [ManagerName]
from @t t1

回答3:

Self join the table

case 1: all employees who have managers

select e1.empname employee, e2.empname manager
from employee e1
join employee e2 on e1.managerId = e2.empid
order by e2.name,e1.name

case 2: all employees who have managers or not

select e1.empname employee, COALESCE(e2.empname,'none') manager
from employee e1
left join employee e2 on e1.managerId = e2.empid
order by e2.name,e1.name

回答4:

Ok, so you asked for other ways. This is a little freaky.

CREATE TABLE employee (empid int, empname varchar(20), managerID int)
GO
insert into employee 

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

CREATE FUNCTION [dbo].[GetEmployeeTree](@ManagerId int)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT 
BEGIN RETURN 
  (SELECT   empID as '@Id', 
            empname AS '@Name',
            dbo.GetEmployeeTree(empid) 
   FROM employee em
   WHERE ManagerId=@ManagerId
   FOR XML PATH('Employee'), TYPE)
END

GO
SELECT  empID as '@Id', 
        empname AS '@Name',
        dbo.GetEmployeeTree(empId) 
FROM employee
WHERE managerId is null
FOR XML PATH('Employee'), ROOT('Employees')

Which gives this output

<Employees>
  <Employee Id="1" Name="A">
    <Employee Id="2" Name="B">
      <Employee Id="4" Name="D" />
    </Employee>
    <Employee Id="3" Name="C" />
  </Employee>
</Employees>

I have actually used this to generate large XML trees with tens of thousands of nodes and it is quite quick. There is probably a way to merge the root query with the child query, I just haven't quite figured that out yet. When I have used this technique in the past I have used a separate link and node table to define the hierarchy and it works a bit cleaner when you do that.