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

收藏

本帖最后由 James.Lv 于 2023-1-5 17:55 编辑

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

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

对比可以发现，我们在主节点，层级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
)
SELECT pid,withTabletest.名称 ,sum(tmp.数值)
FROM tmp
inner join withTabletest on tmp.pid = withTabletest.ID
group by pid,withTabletest.名称

复制代码

效果如图

nimotea · nimotea

科普下另外的学习扩展
这里的 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 关联到所有的子孙数据集然后求和即可。

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

Tony.Fu · Tony.Fu

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

优秀

		自动登录	找回密码
密码			立即注册

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

2 个回复

活字格认证

活字格高级认证

Wyn高级认证

Wyn认证