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

点击(此处)折叠或打开

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

http://bbs.csdn.net/topics/340078327

ITPub博客

树形结构的存储与查询

最新文章