数据库设计中常常会遇到需要存储树形结构,比如员工关系表、组织结构表,等等。
--测试数据
CREATE TABLE #Employees(
EmployeeCode varchar(20) NOT NULL PRIMARY KEY CLUSTERED,
ReportToCode varchar(20) NULL)
GO
INSERT INTO #Employees VALUES('A',NULL)
INSERT INTO #Employees VALUES('B','A')
INSERT INTO #Employees VALUES('C','A')
INSERT INTO #Employees VALUES('D','A')
INSERT INTO #Employees VALUES('E','B')
INSERT INTO #Employees VALUES('F','B')
INSERT INTO #Employees VALUES('G','C')
INSERT INTO #Employees VALUES('H','D')
INSERT INTO #Employees VALUES('I','D')
INSERT INTO #Employees VALUES('J','D')
INSERT INTO #Employees VALUES('K','J')
INSERT INTO #Employees VALUES('L','J')
INSERT INTO #Employees VALUES('M','J')
INSERT INTO #Employees VALUES('N','K')
GO
/*
可能遇到的查询问题:
1. 员工'D'的所有直接下属
2. 员工'D'的所有2级以内的下属(包括直接下属和直接下属的下属)
3. 员工'N'的所有上级(按报告线顺序列出)
4. 员工@EmployeeCode的所有@LevelDown级以内的下属(@EmployeeCode和@LevelDown以变量传入)
DECLARE @EmployeeCode varchar(20), @LevelDown int;
SET @EmployeeCode = 'D';
SET @LevelDown = 2;
5. 员工@EmployeeCode的所有@LevelUp级以内的上级(@EmployeeCode和@LevelUp以变量传入)
DECLARE @EmployeeCode varchar(20), @LevelUp int;
SET @EmployeeCode = 'N';
SET @LevelUp = 2;
*/
--用递归CTE实现员工树形关系表
WITH CTE AS(
SELECT
EmployeeCode,
ReportToCode,
ReportToDepth = 0,
ReportToPath = CAST('/' + EmployeeCode + '/' AS varchar(200))
FROM #Employees
WHERE ReportToCode IS NULL
UNION ALL
SELECT
e.EmployeeCode,
e.ReportToCode,
ReportToDepth = mgr.ReportToDepth + 1,
ReportToPath = CAST(mgr.ReportToPath + e.EmployeeCode + '/' AS varchar(200))
FROM #Employees e
INNER JOIN CTE mgr
ON e.ReportToCode = mgr.EmployeeCode
)
SELECT * FROM CTE ORDER BY ReportToPath