撰寫一個遞回的 CTE，讓他們的經理獲得員工。包括以下列：
- 員工姓氏
- 員工名字
- 部門編號
- 檔案夾
- 經理姓氏
- 經理名字
呼叫的列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'
);
這就是我所做的，我知道它不正確，因為當我SELECT為此 CTE 撰寫陳述句時，我沒有得到表中顯示的任何資料：
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;
uj5u.com熱心網友回復：
此腳本可為您提供您描述的所需輸出。在CREATE表陳述句中，為簡單起見，我洗掉了外鍵約束，因為我使用 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 |
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/533439.html