多层级部门结构展示与分级汇总 |
您所在的位置:网站首页 › 公司领导层级分布图 › 多层级部门结构展示与分级汇总 |
(一)问题的提出 报表设计中,经常涉及层次结构的数据,比如产品的多级分类、组织的多级部门。如何展示层级结构本身,并分级汇总相关的数值数据,是报表设计人员经常面对的挑战。 下面是一个典型的部门表:
层级关系的关键是【上级部门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,如果是一个非叶节点部门,查询结果将是该部门及其所有下属部门的业绩列表。 查询所有部门的业绩结果如下图:
这个查询结果可以直接用作报表设计中的数据集,报表效果如下图:
这就是真正符合用户预期的报表效果。
|
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |