【报表-布局设计】巧用with as函数，实现报表多级数据合计

前言:有时我们的数据纪录只有在叶子节点，如下图:


而报表需要基于层级做汇总，例如这样的样式:

对比可以发现，我们在主节点，层级A，层级B上原本是没有数据的
如需要汇总，可以用这样的方式做递归汇总

1.   WITH tmp AS (
   
2.     SELECT t.Id pid, * FROM withTabletest t
   
3.     UNION ALL
   
4.     SELECT t2.pid tm, t1.* FROM withTabletest t1 JOIN tmp t2 ON t1.ParentId = t2.Id
   
5. )
   
6. 
   
7. SELECT  pid,withTabletest.名称 ,sum(tmp.数值)
   
8. FROM    tmp
   
9. inner join withTabletest on tmp.pid = withTabletest.ID
   
10. group by pid,withTabletest.名称

复制代码

效果如图

科普下另外的学习扩展
这里的 with.as 语法叫做公用表表达式(Common Table Expression)，一般的 cte 我理解为 一种结果集变量，语法为

1. with 表达式名称(传递的列值)
   
2. as
   
3. ( cte 查询结果集)
   

复制代码

当定义了一种结果集之后可以在其他地方多次调用，使sql语句更加简洁(有点像灵活版视图),
版主这篇文章使用的是cte 中比较特殊的 递归cte, 递归cte 的语法稍有区别

1. with 表达式名称(传递的列值)
   
2. as
   
3. (
   
4.    初始查询结果
   
5. union all
   
6.    递归查询结果
   
7. )

复制代码

这里可以展开说下， 这里先假定 这个递归cte的最终结果集是 result_set；在执行这个表达式内容时候，首先把 初始查询结果的内容加入到 result_set 中, 然后会不断的执行 上图中所讲的递归查询结果，如果有生成新的结果集, 就把新的结果集内容添加到 result_set 中，如果没有新的结果集生成了那就终止递归，返回result_set;
结合版主的例子说下
定义了一个递归表达式为 tmp 假设其结果集为 result_set；
首先把初始查询结果内容添加到result_set 中去 也就是

1. select t.pid,* from withTabletest t

复制代码

这就是 result_set 的初始值。然后开始执行递归查询部分

1. select t2.pid,t1.* from withTabletest t1 join tmp t2 on t1.ParentId = t2.Id

复制代码

这段里面的 tmp 实际上就是 上一步计算出来的 result_set 值, 经过这段 sql 查询之后获得的结果 会再加入到result_set 中去，这样就可以不断地递归给result_set中添加新的内容，直到没有新的结果集生成。
从sql的业务意义再来看这段递归sql, 初始set 内容其实就是 全部的父子关系结果, 分析递归sql 不难看出，这部分的效果是在原有父子关系的结果链末尾新加了孙的关系，最终得到的就是父和孙的关系，以及孙的相关信息。 同理在下一次迭代中就会给孙后再加上重孙的关系链，然后把父重孙的关系存到结果集合里面。
所以最后我们得到的是一条条祖先和子孙的关系集合，result_set 里面就是 所有的 |祖先|子孙|子孙信息|  这样结构的数据集

其实到这里关于递归 cte的理解就说完了，再说下最后怎么把 每个节点的 数值聚合求和查询吧, 其实也简单，有了所有祖先子孙关系数据集后，只需要用自身id 关联到 所有的子孙数据集 然后求和即可。

以上就是本人学习后的一些理解，分享出来共勉

nimotea 发表于 2022-10-20 14:48
科普下另外的学习扩展
这里的 with.as 语法叫做公用表表达式(Common Table Expression)，一般的 cte 我理 ...

优秀