【报表-布局设计】巧用with as函数，实现报表多级数据合计
本帖最后由 James.Lv 于 2023-1-5 17:55 编辑前言:有时我们的数据纪录只有在叶子节点，如下图:
而报表需要基于层级做汇总，例如这样的样式:
对比可以发现，我们在主节点，层级A，层级B上原本是没有数据的
如需要汇总，可以用这样的方式做递归汇总
WITH tmp AS (
SELECT t.Id pid, * FROM withTabletest t
UNION ALL
SELECT t2.pid tm, t1.* FROM withTabletest t1 JOIN tmp t2 ON t1.ParentId = t2.Id
)
SELECTpid,withTabletest.名称 ,sum(tmp.数值)
FROM tmp
inner join withTabletest on tmp.pid = withTabletest.ID
group by pid,withTabletest.名称
效果如图
科普下另外的学习扩展
这里的 with.as 语法叫做公用表表达式(Common Table Expression)，一般的 cte 我理解为 一种结果集变量，语法为
with 表达式名称(传递的列值)
as
( cte 查询结果集)
当定义了一种结果集之后可以在其他地方多次调用，使sql语句更加简洁(有点像灵活版视图),
版主这篇文章使用的是cte 中比较特殊的 递归cte, 递归cte 的语法稍有区别
with 表达式名称(传递的列值)
as
(
初始查询结果
union all
递归查询结果
)这里可以展开说下， 这里先假定 这个递归cte的最终结果集是 result_set；在执行这个表达式内容时候，首先把 初始查询结果的内容加入到 result_set 中, 然后会不断的执行 上图中所讲的递归查询结果，如果有生成新的结果集, 就把新的结果集内容添加到 result_set 中，如果没有新的结果集生成了那就终止递归，返回result_set;
结合版主的例子说下
定义了一个递归表达式为 tmp 假设其结果集为 result_set；
首先把初始查询结果内容添加到result_set 中去 也就是
select t.pid,* from withTabletest t这就是 result_set 的初始值。然后开始执行递归查询部分
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 我理 ...
:hjyzw:优秀
页:
[1]