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

前一篇文章中已经可以取得所有部门的全称,但现在又有个新的需求: 只想得到某一个部门的部门全称,虽然可以用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)  -- 销售部/售后科/客服部

 

posted @ 2014-01-11 22:38  goto13  阅读(...)  评论(...编辑  收藏