8.2 GROUPING运算符

同时计算出合计值

理想结果数据集：

 一般查询结果集：

SELECT shohin_bunrui, SUM(hanbai_tanka) FROM Shohin
GROUP BY shohin_bunrui;

可以看出，代码能够得到的也就是GROUP BY分类得到的分类值的求合。

由于GROUP BY子句是用来指定聚合键的场所，所以这里指定的键只能用来进行分割数据。而合计行是不指定聚合键时得到的聚合结果，因此与之下3行通过聚合键得到的结果并不相同。按照通常的思路，想一次得到这两种结果是不可能的。

如果想要获得那样的结果，过去通常会分别计算出合计行和按照商品种类进行聚合的结果，然后通过UNION ALL将它们连接在一起。

SELECT '合计' AS shohin_bunrui, SUM(hanbai_tanka) 
FROM Shohin

UNION ALL

SELECT shohin_bunrui, SUM(hanbai_tanka) 
FROM Shohin
GROUP BY shohin_bunrui;

这样一来，为了得到想要的结果，需要执行2次几乎相同的 SELECT语句，再将其结果进行连接，十分繁琐！

ROLLUP函数

同时计算出合计值和小计值

GROUPING包含3种运算符：CROLLUP、CUBE、GROUPING SETS

ROLLUP的使用方法

SELECT shohin_bunrui, SUM(hanbai_tanka) AS sum_tanka
FROM shohin
GROUP BY ROLLUP(shohin_bunrui);

 在语法上，就是将GROUP BY子中的聚合键清单像 ROLLUP(<列1>，列2>，……)这样进行使用。该运算符的作用，一言以蔽之，就是“一次计算出不同聚合键的组合结果”。

将“登记日期”添加到聚合键当中

一般情况：

SELECT shohin_bunrui, torokubi, SUM(hanbai_tanka) AS sum_tanka
FROM shohin
GROUP BY shohin_bunrui, torokubi;

 加入ROLLUP函数后：

SELECT shohin_bunrui, torokubi, SUM(hanbai_tanka) AS sum_tanka
FROM shohin
GROUP BY ROLLUP(shohin_bunrui, torokubi);

 很明显，可以看出来，ROLLUP函数的作用就是分组后对每个组进行求合。

GROUPING函数

让NULL更加容易分辨

不知道有没有发现，上面的数据中有一部分，见下图，看快了很容易看错，比如把4000看作分组求合的值。

SQL里面提供了一个解决方法 ，没错就是GROUPING函数

函数它能在其参数列的值为超级分组记录所产生的NULL时，返回1，其他情况，返回0。

SELECT GROUPING(shohin_bunrui) AS shohin_bunrui, 
       GROUPING(torokubi) as torokubi,
       SUM(hanbai_tanka) AS sum_tanka
FROM shohin
GROUP BY ROLLUP(shohin_bunrui, torokubi);

 这样就能判断出是超级分组记录中的NULL，还是原始数据本身为NULL了。

如果觉得上面的这种不好看，那么美化一下

SELECT CASE WHEN GROUPING(shohin_bunrui) = 1
            THEN '商品种类 合计' 
            ELSE shohin_bunrui END AS shohin_bunrui, 
       CASE WHEN GROUPING(torokubi) = 1
            THEN '登录日期 合计' 
            ELSE CAST(torokubi AS VARCHAR(16)) END AS torokubi,
       SUM(hanbai_tanka) AS sum_tanka
FROM shohin
GROUP BY ROLLUP(shohin_bunrui, torokubi);

 注意：为什么还要将SELECT子句中的torokubi列转换为CAST(torokubi AS VARCHAR(16))形式的符串呢？这是为了满足CASE表达式所有分支的返回值必须一致的规定。如果不这样的话，那么各个分支会分别返回日期类型和字符串类型的值，执行时就会发生语法错误。

CUBE函数

SELECT CASE WHEN GROUPING(shohin_bunrui) = 1
            THEN '商品种类 合计' 
            ELSE shohin_bunrui END AS shohin_bunrui, 
       CASE WHEN GROUPING(torokubi) = 1
            THEN '登录日期 合计' 
            ELSE CAST(torokubi AS VARCHAR(16)) END AS torokubi,
       SUM(hanbai_tanka) AS sum_tanka
FROM shohin
GROUP BY CUBE(shohin_bunrui, torokubi);

看，前面多出来的几项数据，这几项数据是通过下图的形式构成的

 CUBE，正常情况是3轴的，但是这里展示的只有2轴。不影响我们理解。

GROUPING SETS函数

很少用，看不懂正常

该运算符可以用于从ROLLUP或者CUBE的结果中取出部分记录。

之前的CUBE的结果就是根据聚合键的所有可能组合计算而来的。如果希望从中选取出“商品种类”和“登记日期”各自作为聚合键的结果，反之，不想得到“合计记录和使用2个聚合键的记录”时，可以使用GROUPING SETS。

SELECT CASE WHEN GROUPING(shohin_bunrui) = 1
            THEN '商品种类 合计' 
            ELSE shohin_bunrui END AS shohin_bunrui, 
       CASE WHEN GROUPING(torokubi) = 1
            THEN '登录日期 合计' 
            ELSE CAST(torokubi AS VARCHAR(16)) END AS torokubi,
       SUM(hanbai_tanka) AS sum_tanka
FROM shohin
GROUP BY GROUPING SETS(shohin_bunrui, torokubi);

上述结果中也没有全体的合计行(16780日元)。与ROLL UP或者CUBE能够得到规定的(业务上成为“固定的”)结果相对，GROUPING SETS用于从中取出个别条件对应的不固定的结果。然而，由于期望获得不固定结果的情况少之又少，所以与ROLLUP或者CUBE比起来使用GROUPING SETS的机会也就很少了。

完结撒花~