【Oracle】通过 rollup 和 grouping 函数生成报表

文中使用的Oracle版本为11g。

记录一次之前工作中使用Oracle的rollup和grouping函数生成报表的经历。

临近下班时上级领导要求5分钟内要弄数据出来汇报（往往都是临下班才安排工作的），当时我拿到的模板格式是这样的，如下图：

看到报表结构时首先想到的是要将数据按照部门分组并按照月份做行转列处理。

至于“小计”嘛，大不了可以将前面12个月的列加起来解决（偷个懒）。

最大的问题就在于“合计”那一行，一开始想到的是使用group by + union all处理（实现思路：先将各部门数据统计成一个数据集，合计一行作为另一个数据集用group by统计起来，然后再用union all将两边的数据整合到一起）。

这样做虽然可以但感觉不太方便，后来发现Oracle中本来就提供了一个统计函数rollup来进行这样的统计，下面将结合语句说明一下。

其实rollup说白了就是“列”数据汇总，如果只是单纯的group by得出来的数据只是普通的分组数据集，如下图：

加上了rollup后数据集下面就会自动加上列的汇总统计行，也就是我们平常看到的合计一行，如下图：

虽然报表内容出来了，但是还缺少了一些东西，再对比一下模板就会发现在**“dept”一列最下应该要有“合计”两字，但最底一行是rollup**自动生成出来的要怎样将合计加上呢？

在这个时候grouping函数就出来了。

grouping函数有两个返回值1和0。若返回的是1则表示由于rollup函数所产生的空值对应的信息，返回0表示此行对应的这列参未与rollup函数分组汇总活动。下面是截取Oracle官方文档的说法：

“Using a single column as its argument, GROUPING returns 1 when it encounters a NULL value created by a ROLLUP or CUBE operation. That is, if the NULL indicates the row is a subtotal,GROUPING returns a 1. Any other type of value, including a stored NULL, returns a 0.”

因此在语句中使用case when + grouping函数对rollup字段进行修饰，若返回的是1则给它一个“合计”的中文，0则直接输出所以就出现了，如下图：

这段东西，最后得出的结果如下图所示：

大功告成。

Tags：导出oracle数据