树形结构的存储与查询

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

点击(此处)折叠或打开

1. --测试数据
   
2. 
   
3. CREATE TABLE #Employees(
   
4.     EmployeeCode varchar(20) NOT NULL PRIMARY KEY CLUSTERED,
   
5.     ReportToCode varchar(20) NULL)
   
6. GO
   
7. INSERT INTO #Employees VALUES(\'A\',NULL)
   
8. INSERT INTO #Employees VALUES(\'B\',\'A\')
   
9. INSERT INTO #Employees VALUES(\'C\',\'A\')
   
10. INSERT INTO #Employees VALUES(\'D\',\'A\')
    
11. INSERT INTO #Employees VALUES(\'E\',\'B\')
    
12. INSERT INTO #Employees VALUES(\'F\',\'B\')
    
13. INSERT INTO #Employees VALUES(\'G\',\'C\')
    
14. INSERT INTO #Employees VALUES(\'H\',\'D\')
    
15. INSERT INTO #Employees VALUES(\'I\',\'D\')
    
16. INSERT INTO #Employees VALUES(\'J\',\'D\')
    
17. INSERT INTO #Employees VALUES(\'K\',\'J\')
    
18. INSERT INTO #Employees VALUES(\'L\',\'J\')
    
19. INSERT INTO #Employees VALUES(\'M\',\'J\')
    
20. INSERT INTO #Employees VALUES(\'N\',\'K\')
    
21. GO
    
22. /*
    
23. 可能遇到的查询问题：
    
24. 1. 员工\'D\'的所有直接下属
    
25. 2. 员工\'D\'的所有2级以内的下属（包括直接下属和直接下属的下属）
    
26. 3. 员工\'N\'的所有上级（按报告线顺序列出）
    
27. 4. 员工@EmployeeCode的所有@LevelDown级以内的下属（@EmployeeCode和@LevelDown以变量传入）
    
28. DECLARE @EmployeeCode varchar(20), @LevelDown int;
    
29. SET @EmployeeCode = \'D\';
    
30. SET @LevelDown = 2;
    
31. 5. 员工@EmployeeCode的所有@LevelUp级以内的上级（@EmployeeCode和@LevelUp以变量传入）
    
32. DECLARE @EmployeeCode varchar(20), @LevelUp int;
    
33. SET @EmployeeCode = \'N\';
    
34. SET @LevelUp = 2;
    
35. */
    
36. --用递归CTE实现员工树形关系表
    
37. 
    
38. WITH CTE AS(
    
39.     SELECT
    
40.         EmployeeCode,
    
41.         ReportToCode,
    
42.         ReportToDepth = 0,
    
43.         ReportToPath = CAST(\'/\' + EmployeeCode + \'/\' AS varchar(200))
    
44.     FROM #Employees
    
45.     WHERE ReportToCode IS NULL
    
46.     UNION ALL
    
47.     SELECT
    
48.         e.EmployeeCode,
    
49.         e.ReportToCode,
    
50.         ReportToDepth = mgr.ReportToDepth + 1,
    
51.         ReportToPath = CAST(mgr.ReportToPath + e.EmployeeCode + \'/\' AS varchar(200))
    
52.     FROM #Employees e
    
53.     INNER JOIN CTE mgr
    
54.     ON e.ReportToCode = mgr.EmployeeCode
    
55. )
    
56. SELECT * FROM CTE ORDER BY ReportToPath

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22392018/viewspace-1068778/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22392018/viewspace-1068778/