SQL Server 递归CTE，通过经理获取员工

vh0rcniy 于 2022-11-21 发布在其他

关注(0)|答案(1)|浏览(168)

编写一个递归CTE，该CTE将按经理获取员工。包括以下列：

员工姓氏
员工名字
部门ID
档案文件夹
经理姓氏
经理名字

名为FileFolder的列用于存储每个员工的绩效考核。请注意，由于经理还将有权访问直接或间接向其报告的所有人员，因此每个经理的文件夹最终将设置为不仅包含其自己的绩效考核文件，还包含直接向其报告的每个员工的所有子文件夹。为了便于执行此操作，还包括一个名为“文件路径”的列，该列将确定并显示每个员工的文件路径名，文件路径名在子文件夹之间使用Windows样式\，即格式为ManagerFileFolder\EmployeeFileFolder\等。
为了说明这将如何工作，例如，如果我直接向Dev Sainani报告，Dev向Peter Devlin报告，那么我的文件路径将是PeterDevlin\DevSainani\OsamAl
我不确定如何在不改变表的情况下包括不在提供给我的数据库表中的列，以及如何合并上面提到的“文件路径”要求。
这是Employees表（我提供的唯一表）的脚本：

CREATE TABLE dbo.Employees 
(
    EmployeeID        INT IDENTITY PRIMARY KEY,
    DepartmentID      INT 
        CONSTRAINT FK_Employee_Department 
            FOREIGN KEY REFERENCES dbo.Departments (DepartmentID),
    ManagerEmployeeID INT 
        CONSTRAINT FK_Employee_Manager 
            FOREIGN KEY REFERENCES dbo.Employees (EmployeeID),
    FirstName         NVARCHAR(60),
    LastName          NVARCHAR(60),
    Salary            MONEY 
        CONSTRAINT CK_EmployeeSalary CHECK (Salary >= 0),
    CommissionBonus   MONEY 
        CONSTRAINT CK_EmployeeCommission CHECK (CommissionBonus >= 0),
    FileFolder        NVARCHAR(256) 
        CONSTRAINT DF_FileFolder DEFAULT 'ToBeCreated'
);

这就是我所做的，我知道这是不正确的，因为当我为该CTE编写SELECT语句时，没有获得表中显示的任何数据：

WITH GetEmployeeByManager AS 
(
    SELECT 
        FirstName, LastName, DepartmentID, FileFolder
    FROM 
        dbo.Employees
    WHERE 
        ManagerEmployeeID IS NULL

    UNION ALL

    SELECT 
        e.LastName, e.FirstName, e.DepartmentID, e.FileFolder
    FROM 
        Employees e
    JOIN 
        GetEmployeeByManager ge ON e.ManagerEmployeeID = ge.ManagerEmployeeID
)
SELECT *
FROM GetEmployeeByManager ge
JOIN dbo.Employees e ON ge.ManagerEmployeeID = e.ManagerEmployeeID;

sql-server

来源：https://stackoverflow.com/questions/74426304/recursive-cte-to-get-employees-by-their-manager

1条答案

按热度按时间

ghg1uchk1#

在 CREATE table 语句中，为了简单起见，我删除了外键约束，因为我使用 SQL Fiddle 来构造这个查询。
SQL 小提琴

- MS SQL Server 2017 架构设置 * * ：

CREATE TABLE Employees 
(
    EmployeeID        INT not null PRIMARY KEY,
    DepartmentID      INT not null,
    ManagerEmployeeID INT null,
    FirstName         NVARCHAR(60),
    LastName          NVARCHAR(60),
    Salary            MONEY 
        CONSTRAINT CK_EmployeeSalary CHECK (Salary >= 0),
    CommissionBonus   MONEY 
        CONSTRAINT CK_EmployeeCommission CHECK (CommissionBonus >= 0),
    FileFolder        NVARCHAR(256) 
        CONSTRAINT DF_FileFolder DEFAULT 'ToBeCreated'
);

INSERT INTO Employees (EmployeeID, DepartmentID, ManagerEmployeeID, FirstName, LastName, Salary, CommissionBonus, FileFolder)
VALUES (1, 101, null, 'Ted', 'Smith', 12000.00, 120.00, 'TedSmith')
  , (2, 101, 1, 'John','Doe', 10000.00, 100.00, 'JohnDoe')
  , (3, 101, 2, 'Dev', 'Patel', 8000.00, 80.00, 'DevPatel')
;

中的每一个

- 查询 1 * * ：

WITH GetEmployeeByManager AS 
(
    SELECT 
        e.EmployeeID
        , e.FirstName
        , e.LastName
        , e.DepartmentID
        , e.ManagerEmployeeID
        , em.FirstName as ManagerFirstName
        , em.LastName as ManagerLastName
        , e.FileFolder
        , e.FileFolder as FilePath
        , 0 as hierarchy_level
    FROM Employees as e
        LEFT OUTER JOIN Employees as em
            ON em.EmployeeID = e.ManagerEmployeeID
    WHERE e.ManagerEmployeeID is null --First query gets only managers

    UNION ALL

    SELECT 
        e.EmployeeID
        , e.FirstName
        , e.LastName
        , e.DepartmentID
        , e.ManagerEmployeeID
        , em.FirstName as ManagerFirstName
        , em.LastName as ManagerLastName
        , e.FileFolder
        , CAST(em.FilePath + '/' + e.FileFolder as nvarchar(256)) as FileFolder
        , em.hierarchy_level + 1 as hierarchy_level
    FROM Employees e
        INNER JOIN GetEmployeeByManager as em
            ON em.EmployeeID = e.ManagerEmployeeID
    WHERE em.hierarchy_level < 50
)
SELECT *
FROM GetEmployeeByManager ge

格式

- 结果 * * ：

| EmployeeID | FirstName | LastName | DepartmentID | ManagerEmployeeID | ManagerFirstName | ManagerLastName | FileFolder |                  FilePath | hierarchy_level |
|------------|-----------|----------|--------------|-------------------|------------------|-----------------|------------|---------------------------|-----------------|
|          1 |       Ted |    Smith |          101 |            (null) |           (null) |          (null) |   TedSmith |                  TedSmith |               0 |
|          2 |      John |      Doe |          101 |                 1 |              Ted |           Smith |    JohnDoe |          TedSmith/JohnDoe |               1 |
|          3 |       Dev |    Patel |          101 |                 2 |             John |             Doe |   DevPatel | TedSmith/JohnDoe/DevPatel |               2 |

格式

赞(0）回复(0）举报 2022-11-21

我来回答

SQL Server 递归CTE，通过经理获取员工

1条答案

相关问题

热门标签

最新问答