语法
GROUP BY CUBE( (…),(…),…)
功能
对CUBE 后面括号里的n 个字段或表达式组合做GROUP BY 操作,最后将结果合
并在一起,组合方式为n 个字段或表达式的全部子集。
详见下面的解释:
GROUP BY CUBE(A,B,C) (A、B、C 代表语法中的“(…)”)
首先会对(A、B、C)进行GROUP BY,然后依次是(A、B),(A、C),(A),(B、C),
(B),(C),然后对全表进行GROUP BY 操作,最后将所有结果合并在一起(相当于
UNION ALL 操作),如果n 个字段或表达式中的一个或多个在某一分组中不出现在
GROUP BY 后面,用NULL 代替不出现的字段或表达式。
示例
示例1:GROUP BY CUBE(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,NVL(DECODE(color_type,NULL,f_Y
earMonth || '合计',NVL(f_YearMonth,color_type || ' 小计')),'总计') AS f_YearMonth_show,SU
GBase UP 产品手册 5 数据库管理指南
文档版本04(2021-04-21) 南大通用数据技术股份有限公司 575
M(color_count) FROM (SELECT color_type,DATE_FORMAT(in_date, '%Y-%m') as f_Year
Month,color_count FROM t3) t GROUP BY CUBE(color_type,f_YearMonth) ORDER BY col
or_type,f_YearMonth;
+-----------------+------------------+------------------+
| color_type_show | f_YearMonth_show | SUM(color_count) |
+-----------------+------------------+------------------+
| black | 2010-09 | 18 |
| black | 2010-10 | 49 |
| black | black 小计 | 67 |
| blue | 2010-09 | 38 |
| blue | 2010-10 | 62 |
| blue | blue 小计 | 100 |
| red | 2010-09 | 41 |
| red | 2010-10 | 23 |
| red | red 小计 | 64 |
| | 2010-09 合计 | 97 |
| | 2010-10 合计 | 134 |
| | 总计 | 231 |
+-----------------+------------------+------------------+
12 rows in set