Mysql 分组计汇, MySQL中with rollup的用法, group by汇总

Mysql中有一个with rollup是用来在分组统计数据的基础上再进行统计汇总，即用来得到group by的汇总信息；

举例如下：

新建表：

create table age ( sno char(4) primary key,sname varchar(10),sage int);

表中数据有：

mysql> select * from age;
+------+-----------+------+
| sno  | sname     | sage |
+------+-----------+------+
| 1101 | justcode1 |   20 |
| 1102 | justcode2 |   21 |
| 1103 | justcode3 |   22 |
| 1104 | justcode4 |   20 |
| 1105 | justcode5 |   21 |
| 1106 | justcode6 |   21 |
| 1107 | justcode7 |   22 |
| 1108 | justcode8 |   22 |
+------+-----------+------+
8 rows in set (0.00 sec)
--------------------- 

没有with rollup的查询:

mysql> select count(*),sage from age group by sage;
+----------+------+
| count(*) | sage |
+----------+------+
|        2 |   20 |
|        3 |   21 |
|        3 |   22 |
+----------+------+
3 rows in set (0.00 sec)
--------------------- 

带with rollup的查询：

mysql> select count(*),sage from age group by sage with rollup;
+----------+------+
| count(*) | sage |
+----------+------+
|        2 |   20 |
|        3 |   21 |
|        3 |   22 |
|        8 | NULL |
+----------+------+
4 rows in set (0.00 sec)
--------------------- 

with rollup 、with cube、grouping

CUBE 和 ROLLUP 之间的区别在于：

CUBE 生成的结果集显示了所选列中值的所有组合的聚合。

ROLLUP 生成的结果集显示了所选列中值的某一层次结构的聚合。

grouping: 当用 CUBE 或 ROLLUP 运算符添加行时，附加的列输出值为1，当所添加的行不是由 CUBE 或 ROLLUP 产生时，附加列值为0。

--例如
DECLARE @T TABLE(名称 VARCHAR(1) , 出版商 VARCHAR(10),  价格1  INT, 价格2 INT)
INSERT @T
SELECT 'a',  '北京',  11,  22 UNION ALL
SELECT 'a',  '四川',  22,  33 UNION ALL
SELECT 'b',  '四川',  12,  23 UNION ALL
SELECT 'b',  '北京',  10,  20 UNION ALL
SELECT 'b',  '昆明',  20,  30
SELECT
名称,
出版商,
SUM(价格1) AS 价格1,
SUM(价格2) AS 价格2,
GROUPING(名称) AS CHECK名称,
GROUPING(出版商) AS CHECK出版商
FROM @T GROUP BY 名称,出版商 WITH CUBE

名称   出版商        价格1         价格2         CHECK名称 CHECK出版商
---- ---------- ----------- ----------- ------- --------
a    北京         11          22          0       0
a    四川         22          33          0       0
a    NULL       33          55          0       1
b    北京         10          20          0       0
b    昆明         20          30          0       0
b    四川         12          23          0       0
b    NULL       42          73          0       1
NULL NULL       75          128         1       1
NULL 北京         21          42          1       0
NULL 昆明         20          30          1       0
NULL 四川         34          56          1       0
（所影响的行数为 11 行）
*/
--分析
/*group by 两列：名称有两个类别A,B;所有由CUBE运算而生成行的是
名称   出版商        价格1         价格2         CHECK名称 CHECK出版商
---- ---------- ----------- ----------- ------- --------
a    NULL       33          55          0       1
b    NULL       42          73          0       1
出版商有三个类别，所有由CUBE运算而生成行的是
名称   出版商        价格1         价格2         CHECK名称 CHECK出版商
---- ---------- ----------- ----------- ------- --------
NULL 北京         21          42          1       0
NULL 昆明         20          30          1       0
NULL 四川         34          56          1       0
以及
NULL NULL       75          128         1       1

SELECT
名称,
出版商,
SUM(价格1) AS 价格1,
SUM(价格2) AS 价格2
FROM @T GROUP BY 名称,出版商 WITH ROLLUP

名称   出版商        价格1         价格2
---- ---------- ----------- -----------
a    北京         11          22
a    四川         22          33
a    NULL       33          55
b    北京         10          20
b    昆明         20          30
b    四川         12          23
b    NULL       42          73
NULL NULL       75          128

本文：Mysql 分组计汇, MySQL中with rollup的用法, group by汇总