(一)问题的提出
报表设计中,经常涉及层次结构的数据,比如产品的多级分类、组织的多级部门。如何展示层级结构本身,并分级汇总相关的数值数据,是报表设计人员经常面对的挑战。
下面是一个典型的部门表:
层级关系的关键是【上级部门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,如果是一个非叶节点部门,查询结果将是该部门及其所有下属部门的业绩列表。
查询所有部门的业绩结果如下图:
这个查询结果可以直接用作报表设计中的数据集,报表效果如下图:
这就是真正符合用户预期的报表效果。