GROUP BY GROUPING SETS 函数
语法
GROUP BY GROUPING SETS( (…),(…),…)
功能
对GROUPING SETS 后面括号里的n 个字段或表达式分别做GROUP BY 操作,
最后将结果合并在一起。
详细解释
GROUP BY GROUPING SETS (A,B,C) (A、B、C 代表语法中的“(…)”)
首先对(A)进行GROUP BY,然后对(B)进行GROUP BY,然后对(C)进行GROUP
BY,最后将所有结果合并在一起(相当于UNION ALL 操作),如果n 个字段或
表达式中的一个或多个在某一分组中不出现在GROUP BY 后面,用NULL 代替
不出现的字段或表达式。
示例
示例中所用的表及数据:
DROP TABLE IF EXISTS t3;
GBase 8a MPP Cluster 产品手册
5 数据库管理指南
文档版本953(2022-09-15)
南大通用数据技术股份有限公司
884
CREATE TABLE t3 (color_type varchar(20),color_count int, in_date date);
INSERT INTO t3 (color_type,in_date,color_count)
VALUES('black','2010-09-11',18),
('black','2010-10-05',18),('black','2010-10-13',31),
('blue','2010-09-21',23),('blue','2010-09-30',15),
('blue','2010-10-11',62),('red','2010-09-12',41),
('red','2010-10-01',12),('red','2010-10-05',11);
示例1:GROUP BY GROUPING SETS(color_type,f_YearMonth)
gbase> SELECT color_type,in_date,color_count FROM t3 ORDER BY
color_type,in_date;
+------------+------------+-------------+
| color_type | in_date
| color_count |
+------------+------------+-------------+
| black
| 2010-09-11 |
18 |
| black
| 2010-10-05 |
18 |
| black
| 2010-10-13 |
31 |
| blue
| 2010-09-21 |
23 |
| blue
| 2010-09-30 |
15 |
| blue
| 2010-10-11 |
62 |
| red
| 2010-09-12 |
41 |
| red
| 2010-10-01 |
12 |
| red
| 2010-10-05 |
11 |
+------------+------------+-------------+
9 rows in set
gbase> SELECT NVL(color_type,'') as color_type_show,DECODE(color_ty
pe,NULL,f_YearMonth || '合计',NVL(f_YearMonth,color_type || ' 小计'))
AS f_YearMonth_show,SUM(color_count) FROM (SELECT color_type,DA
TE_FORMAT(in_date, '%Y-%m') as f_YearMonth,color_count FROM t3)
t GROUP BY GROUPING SETS(color_type,f_YearMonth) ORDER BY
color_type,f_YearMonth;
+-----------------+------------------+------------------+
| color_type_show | f_YearMonth_show | SUM(color_count) |
+-----------------+------------------+------------------+
| black
| black 小计
|
67 |
| blue
| blue 小计
|
100 |
| red
| red 小计
|
64 |
|
| 2010-09 合计
|
97 |
|
| 2010-10 合计
|
134 |
+-----------------+------------------+------------------+
5 rows in set
GBase 8a MPP Cluster 产品手册
5 数据库管理指南
文档版本953(2022-09-15)
南大通用数据技术股份有限公司
885