【Oracle】结合rollup生成报表

超、凢脫俗 2022-08-09 12:44 171阅读 0赞

领导一进来就将模板丢到面前，说5分钟之内给我弄数据出来，拿到的模板格式是这样的

![这里写图片描述][037328e2020c4f9c1c9f7b7abeafa74a.png]

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

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

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

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

![这里写图片描述][ce28e3f38759bdfb4b8a6c017b377280.png]

其实rollup说白了就是列的数据汇总，如果没有使用的话得出来的数据只是普通的分组数据集，如下图

![这里写图片描述][357a7278c339d2e872f56abe42c2f8a2.png]

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

![这里写图片描述][83a3971c6d1764598b40bb301239db89.png]

这样报表虽然出来了但是还是缺少了一些东西，再对比一下模板就会发现在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则直接输出所以就出现了

![这里写图片描述][16ce4de1eb261a1a379d20e51aafd054.png]

这段东西，最后得出的结果是

![这里写图片描述][6cf44db267230a38a6d0da8f1d42faf2.png]

[037328e2020c4f9c1c9f7b7abeafa74a.png]: /images/20220731/0543e7249e67446e9d00e9e67d79d5a5.png
[ce28e3f38759bdfb4b8a6c017b377280.png]: /images/20220731/1d20edcba0c44952831f19519822b140.png
[357a7278c339d2e872f56abe42c2f8a2.png]: /images/20220731/0b34200615434732baa00a2ddbde7766.png
[83a3971c6d1764598b40bb301239db89.png]: /images/20220731/70c67564a8dc461ea808ca19db88b3d1.png
[16ce4de1eb261a1a379d20e51aafd054.png]: /images/20220731/a8f996dc39f54eb0be73a908f9932dfa.png
[6cf44db267230a38a6d0da8f1d42faf2.png]: /images/20220731/da151f11dd6c46dd915976d467c91dd3.png