SQL笔记 - CTE递归实例:显示部门全称

昨天在整理JS的Function时,示例是一个递归函数。说起递归,想起前段时间在搞CTE,那个纠结呀,看似容易,可我总抓不住门道,什么递归条件,什么结束条件,一头雾水。。。今天一大早就爬起来,果然不负有心人,顺利地完成,废话不多说。

场景: 有一个部门表:部门ID,部门名称,父部门ID,部门层次,需要显示部门的全名称(一级部门和二级部门直接显示,其余的格式:父部门名称+/本部门名称)

先做个测试,递归实现部门层次

 1 -- To implement a Recursion using CTE
 2 ;    -- Separate to other sentence
 3 With CTE_Unit_Level        -- test: show the level of every Unit
 4 as(
 5     select UnitID, UnitName, ParentUnitID, UnitLevel, 1 as ULevel 
 6         from Unit
 7         where ParentUnitID = 0
 8     Union All    -- Essential
 9     select U.UnitID, U.UnitName, U.ParentUnitID, U.UnitLevel,(CU.ULevel + 1) as ULevel
10         from Unit as U
11             Inner Join CTE_Unit_Level as CU
12             on U.ParentUnitID = CU.UnitID    
13 )
14 -- show the result
15 select * from CTE_Unit_Level

结果完全一致:(这次竟然一下子就OK了,不可思议哦)

下面就是显示部门名称:可以用UnitName 累加,加就加呗, 但是需要设置下部门名称的类型

 1 ;
 2 With CTE_Unit_Name        -- test: show the full name of every Unit
 3 as(
 4     select UnitID, 
 5             --UnitName, 
 6             Cast(UnitName as nvarchar(max)) as UnitName,  --Essential!
 7             ParentUnitID
 8         from Unit
 9         where ParentUnitID = 0
10     Union All    -- Essential
11     select U.UnitID, 
12             (
13                 (
14                     Case CU.ParentUnitID
15                         When 0 Then ''
16                         Else CU.UnitName + '/'
17                     End
18                 ) + U.UnitName
19             ) as UnitName, 
20             U.ParentUnitID
21         from Unit as U
22             Inner Join CTE_Unit_Name as CU
23             on U.ParentUnitID = CU.UnitID    
24 )
25 select * from CTE_Unit_Name

执行前,先插入个四级部门:

1 -- insert a unit of Level 4
2 insert into Unit
3     values('客服部', 8, 4)

结果:

------ Perfect! ~\(≧▽≦)/~

参考:

http://www.cnblogs.com/xfrog/archive/2010/10/10/1847462.html

http://www.cnblogs.com/CareySon/archive/2011/12/12/2284740.html

http://www.cnblogs.com/nokiaguy/archive/2009/01/31/1381562.html

posted @ 2014-01-11 21:49  goto13  阅读(652)  评论(1编辑  收藏