多层级部门结构展示与分级汇总

报表设计中，经常涉及层次结构的数据，比如产品的多级分类、组织的多级部门。如何展示层级结构本身，并分级汇总相关的数值数据，是报表设计人员经常面对的挑战。

下面是一个典型的部门表：

层级关系的关键是【上级部门ID】字段，该字段值为NULL的部门，就是顶级部门（根节点）。

关联的业务数据【部门业绩】表如下图：

其中，【部门ID】是引用部门表ID字段的外键字段。

表中记录只有最底层部门（叶节点）的销售业绩数据，上级部门的销售业绩需要根据层级关系逐级汇总。

网上推荐的做法是使用CTE递归查询：

with cte as

(

select [ID]

      ,[部门编码]

      ,[部门名称]

      ,[上级部门ID],0 as lvl from 部门

where [上级部门ID] is null

union all

select d.[ID]

      ,d.[部门编码]

      ,d.[部门名称]

      ,d.[上级部门ID],lvl+1 from cte c inner join 部门 d

on c.Id = d.[上级部门ID]

)

select * from cte

结果如下图：

这个查询结果可以清晰显示部门上下级关系，以及每个部门所处的层级。其中的lvl字段就是部门的层级，顶级部门层级为0。

但是要满足报表设计的要求，还需要解决几个问题：

（1）部门的显示次序，应按照上下级关系排列。上图中，【总务部】下属的【财务部】和【后勤部】应显示在第2、3行，而非图中显示的末尾。

（2）部门业绩应按部门上下级关系逐级汇总。

使用上面的递归查询，即使可以解决，也存在不易理解的问题。因此，下面采用一种通俗易懂的方法来逐步解决这些问题。

（1）部门排序问题

报表用户期待的部门排序方式，是一种按层级缩进的树状结构，如下图：

要实现这样的排序，需要保证一个部门的所有下级部门都排在这个部门的下面，并且仅当这个部门的子树全部显示完毕，才会显示其他部门。为此，我们引入一个【部门全路径】的概念，代表每个部门从根节点开始的各层级部门的组合，如下图：

以图中【西北一部】为例，该部门本身的ID是15，上级部门是【5-运营中心】\【7-销售部】\【10-西北大区】，因此部门全路径的ID就是-5-7-10-15，见图中的IDPath字段。

为了得到全路径部门ID，首先做几个CTE，分别是每个级别的部门列表，部门总共有几级，就做几个CTE，语句如下：

with CTE1 as

(

 select 1 Lvl, T.ID, T.部门编码, T.部门名称,T.上级部门ID

, ID ID1, null ID2, null ID3, null ID4, null ID5, null ID6

 from 部门 T where [上级部门ID] is null

)

, CTE2 as

(

 select 2 Lvl, T.ID, T.部门编码, T.部门名称,T.上级部门ID

, CTE1.ID1, T.ID ID2, null ID3, null ID4, null ID5, null ID6

 from 部门 T inner join CTE1 on T.[上级部门ID]=CTE1.ID

)

, CTE3 as

(

 select 3 Lvl, T.ID, T.部门编码, T.部门名称,T.上级部门ID

, CTE2.ID1,CTE2.ID2,T.ID ID3,null ID4,null ID5,null ID6

 from 部门 T inner join CTE2 on T.[上级部门ID]=CTE2.ID

)

,CTE4 as

(

 select 4 Lvl, T.ID, T.部门编码, T.部门名称,T.上级部门ID

, CTE3.ID1,CTE3.ID2,CTE3.ID3,T.ID ID4,null ID5,null ID6

 from 部门 T inner join CTE3 on T.[上级部门ID]=CTE3.ID

)

,CTE5 as

(

 select 5 Lvl, T.ID, T.部门编码, T.部门名称,T.上级部门ID

 ,CTE4.ID1,CTE4.ID2,CTE4.ID3,CTE4.ID4,T.ID ID5,null ID6

 from 部门 T inner join CTE4 on T.[上级部门ID]=CTE4.ID

)

,CTE6 as

(

 select 6 Lvl, T.ID, T.部门编码, T.部门名称,T.上级部门ID

, CTE5.ID1,CTE5.ID2,CTE5.ID3,CTE5.ID4,CTE5.ID5,T.ID ID6

 from 部门 T inner join CTE5 on T.[上级部门ID]=CTE5.ID

)

以上是按6级部门设计的6个CTE。

下一步，将各个级别的部门使用UNION合并在一起：

,CTE_Levels as

(

select * from CTE1

union

select * from CTE2

union

select * from CTE3

union

select * from CTE4

union

select * from CTE5

union

select * from CTE6

)

此时，select * from CTE_Levels的结果如下：

以此为基础，定义新的CTE，就可以查到每个部门的全路径ID了：

,CTE_Path as

(

select

 L.ID1,T1.部门名称 名称

,L.ID2,T2.部门名称 名称

,L.ID3,T3.部门名称 名称

,L.ID4,T4.部门名称 名称

,L.ID5,T5.部门名称 名称

,L.ID6,T6.部门名称 名称

,L.ID

,L.部门名称 名称

,Lvl

,'-' + convert(nvarchar,ID1)

 + '-' + isnull(convert(nvarchar,ID2),'')

 + '-' + isnull(convert(nvarchar,ID3),'')

 + '-' + isnull(convert(nvarchar,ID4),'')

 + '-' + isnull(convert(nvarchar,ID5),'')

 + '-' + isnull(convert(nvarchar,ID6),'') + '-' IDPath

from CTE_Levels L

 left join 部门 T1 on L.ID1=T1.ID

 left join 部门 T2 on L.ID2=T2.ID

 left join 部门 T3 on L.ID3=T3.ID

 left join 部门 T4 on L.ID4=T4.ID

 left join 部门 T5 on L.ID5=T5.ID

 left join 部门 T6 on L.ID6=T6.ID

)

查询这个CTE，以全路径为排序依据：

select * from CTE_Path order by IDPath

结果如下：

这样的排列次序，就是符合预期的排序效果了。

（2）逐级汇总问题

基于全路径部门的CTE，定义新的CTE，使用JOIN，将部门信息与业绩数据关联起来：

,CTEx as

(

select T.ID,T.名称,T.IDPath,Lvl

,isnull(sum(M.销售业绩),0) 销售业绩

from CTE_Path T

 left join 部门业绩 M on M.部门ID in

  (select ID from CTE_Path where IDPath like '%-'

 + convert(nvarchar,T.ID) +'-%'  )

group by T.ID,T.名称,T.IDPath,Lvl

)

使用下面的查询语句来查询上面定义的CTE，可轻松实现销售业绩按部门分级汇总：

select ID,IDPath

,REPLICATE('__',lvl-1) + 名称 缩进名称

,销售业绩

 from CTEx

--where IDPath like  ('%-' + convert(nvarchar,@DepID) +'-%')

order by IDPath

注意其中【缩进名称】的获取方法，借助CTE中代表部门级次的lvl字段值，在部门名称前添加不同数量的空格，级别越低的部门，前缀空格越多。

另请注意上述查询语句中注释掉的where子句，这是为了演示带参数的查询，即查询指定部门的销售业绩的方法。指定的DepID参数可以是任意级别的任意部门的ID，如果是一个非叶节点部门，查询结果将是该部门及其所有下属部门的业绩列表。

查询所有部门的业绩结果如下图：

这个查询结果可以直接用作报表设计中的数据集，报表效果如下图：

这就是真正符合用户预期的报表效果。