树形结构的存储与查询

数据库设计中常常会遇到需要存储树形结构，比如员工关系表、组织结构表，等等。

 

 

--测试数据
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