SQL笔记 - CTE递归实例(续)：显示指定部门的全称

浏览数：42 / 时间：2015年06月12日

前一篇文章中已经可以取得所有部门的全称，但现在又有个新的需求：只想得到某一个部门的部门全称，虽然可以用where条件来过滤，但是会有点小浪费。这时我们可以从后往前找，先看下效果：

最后一条就是，行得通！但是怎么取出来呢？用ParentUnitID排序？但是实际生活中，部门可能调整或归并，并不总是 UnitID > ParentUnitID. 所以需要一个类似于标识列的参照物：

 1 Declare @utid int
 2 Set @utid = 10        -- the target unit
 3 ;
 4 With CTE_Unit_Name_Special        -- test: show the full name of every Unit
 5 as(
 6     select UnitID, 
 7             --UnitName, 
 8             Cast(UnitName as nvarchar(max)) as UnitName,
 9             ParentUnitID,
10             0 as IndexTemp        -- identity
11         from Unit
12         where UnitID = @utid
13     Union All    -- Essential
14     select U.UnitID, 
15             (
16                 U.UnitName + ‘/‘ + CU.UnitName
17             ) as UnitName, 
18             U.ParentUnitID,
19             (CU.IndexTemp + 1) as IndexTemp
20         from Unit as U
21             Inner Join CTE_Unit_Name_Special as CU
22             on U.UnitID = CU.ParentUnitID
23         where U.ParentUnitID != 0    
24 )
25 --select * from CTE_Unit_Name_Special
26 select top 1 * from CTE_Unit_Name_Special
27     --order by ParentUnitID asc    -- only the situation where PUTID < UTID is suited
28     order by IndexTemp desc    -- need a reference substance, like a Identity column

结果不用再显示了。。。等等，刚想起来，部门表中有个列UnitLevel是标识部门父子层次关系的，不管部门怎么调整，这个层次还是有顺序的，可以直接用，一样的。。。。

顺便捎带个以前写的一个函数：

 1 -- Function - get a full unit name of one special unit
 2 Create Function FN_GetFullUnitName(@unitID int)
 3 Returns nvarchar(1000)
 4 as
 5 Begin
 6     Declare @fullName nvarchar(1000),
 7             @level smallint,
 8             @parentUTID int
 9     select @fullName = UnitName,
10            @parentUTID = ParentUnitID,
11            @level = UnitLevel
12     from Unit
13     where UnitID = @unitID
14     
15     if @level <= 2
16         return @fullName
17     
18     while @level > 2
19     Begin
20         Set @unitID = @parentUTID
21         select @fullName = UnitName + ‘/‘ + @fullName,
22                @parentUTID = ParentUnitID,
23                @level = UnitLevel
24         from Unit
25         where UnitID = @unitID
26     End    
27     return @fullName
28 End
29 go
30 --Drop Function FN_GetFullUnitName
31 --select dbo.FN_GetFullUnitName(10)  -- 销售部/售后科/客服部