# GROUPING SETS
#### 功能描述
GROUPING SETS生成交叉表格行，可以实现GROUP BY字段的交叉统计。
#### 语法格式
```
SELECT attr_expr_list FROM table_reference
  GROUP BY col_name_list
  GROUPING SETS(col_name_list);
```
#### 关键字
GROUPING SETS：为对GROUP BY的扩展，例如
- ***SELECT a, b, sum(expression) FROM table GROUP BY a, b GROUPING SETS((a,b));***
  将转换为以下一条查询：
  ```
  SELECT a, b, sum(expression) FROM table
    GROUP BY a, b;
  ```
  
- ***SELECT a, b, sum(expression) FROM table GROUP BY a, b GROUPING SETS(a,b);***
  将转换为以下两条查询：
  ```
  SELECT a, NULL, sum(expression) FROM table GROUP BY a;
  UNION
  SELECT NULL, b, sum(expression) FROM table GROUP BY b;
  ```
  
- ***SELECT a, b, sum(expression) FROM table GROUP BY a, b GROUPING SETS((a,b), a);***
  将转换为以下两条查询：
  ```
  SELECT a, b, sum(expression) FROM table GROUP BY a, b;
  UNION
  SELECT a, NULL, sum(expression) FROM table GROUP BY a;
  ```
  
- ***SELECT a, b, sum(expression) FROM table GROUP BY a, b GROUPING SETS((a,b), a, b, ());***
  将转换为以下四条查询：
  ```
  SELECT a, b, sum(expression) FROM table GROUP BY a, b;
  UNION
  SELECT a, NULL, sum(expression) FROM table GROUP BY a, NULL;
  UNION
  SELECT NULL, b, sum(expression) FROM table GROUP BY NULL, b;
  UNION
  SELECT NULL, NULL, sum(expression) FROM table;
  ```
  
 
#### 注意事项
- 所要分组的表必须是已经存在的表，否则会出错。
- 不同于ROLLUP，GROUPING SETS目前仅支持一种格式。
 
#### 示例
根据group_id与job两个字段生成交叉表格行，返回每种聚合情况下的salary总和。
```
SELECT group_id, job, SUM(salary) FROM group_test
  GROUP BY group_id, job
  GROUPING SETS (group_id, job);
```