学步园

在项目中遇到了一个层级关系，情况如下，要在用户信息表中找到汇报给大区销售经理的员工信息(含汇报给大区销售经理的员工的员工），

1 及：大区销售经理

2级：汇报给大区销售经理的直接汇报对象

3级：汇报给2级的直接对象；

为了以后方便查找此资料，现在我把大区销售经理的id 看成2，T-SQL语句如下：

With temp_1 AS(
SELECT ee.EmployeeID ,ee.LastName +' '+ee.FirstName as Name FROM Employees ee INNER JOIN Employees tt ON tt.EmployeeID = ee.ReportsTo
WHERE  tt.EmployeeID = 2
),
temp_2 AS(
 SELECT EmployeeID,Name FROM temp_1
 UNION 
 SELECT ee.EmployeeID,ee.LastName+' '+ee.FirstName as Name FROM Employees ee INNER JOIN temp_1 tt ON tt.EmployeeID = ee.ReportsTo
)

另一种方式是创建一个sql 的function

CREATE FUNCTION dbo.FnGetReports(@EmpolyeeID    AS INT)
    RETURNS @Reports TABLE
    (
        EmployeeID    INT NOT NULL,
        ReportToID    INT NULL
    )
AS
    BEGIN
        DECLARE @Employee    INT;
        INSERT INTO @Reports SELECT EmployeeID,ReportsTo FROM Employees WHERE EmployeeID=@EmpolyeeID;
        SELECT @Employee=MIN(EmployeeID) FROM Employees WHERE ReportsTo=@EmpolyeeID;
        WHILE @Employee IS NOT NULL
            BEGIN
                INSERT INTO @Reports SELECT * FROM dbo.FnGetReports(@Employee);
                SELECT  @Employee = MIN(EmployeeID)
                FROM  Employees
                WHERE EmployeeID>@Employee
                AND ReportsTo=@EmpolyeeID
            END
        RETURN
    END
GO

调用方式：

DECLARE @EmployeeID    INT;
SELECT @EmployeeID = 2;
SELECT  ee.EmployeeID,ee.LastName+' '+ ee.FirstName,tt.LastName+' '+TT.FirstName AS 'ReportTo'
      FROM Employees AS ee
      INNER JOIN DBO.FnGetReports(@EmployeeID) AS aa ON ee.EmployeeID = aa.EmployeeID
      INNER JOIN Employees tt ON tt.EmployeeID = aa.ReportToID