更新时间:2022-02-22 GMT+08:00

ROLLUP

功能描述

ROLLUP生成聚合行、超聚合行和总计行。可以实现从右到左递减多级的统计,显示统计某一层次结构的聚合。

语法格式

1
2
3
SELECT attr_expr_list FROM table_reference
  GROUP BY col_name_list
  WITH ROLLUP;

关键字

ROLLUP:为GROUP BY的扩展,例如:SELECT a, b, c, SUM(expression) FROM table GROUP BY a, b, c WITH ROLLUP;将转换成以下四条查询:
  • (a, b, c)组合小计
    1
    2
    SELECT a, b, c, sum(expression) FROM table
      GROUP BY a, b, c;
    
  • (a, b)组合小计
    1
    2
    SELECT a, b, NULL, sum(expression) FROM table
      GROUP BY a, b;
    
  • (a)组合小计
    1
    2
    SELECT a, NULL, NULL, sum(expression) FROM table
      GROUP BY a;
    
  • 总计
    1
    SELECT NULL, NULL, NULL, sum(expression) FROM table;
    

注意事项

所要分组的表必须是已经存在的表,否则会出错。

示例

根据group_id与job两个字段生成聚合行、超聚合行和总计行,返回每种聚合情况下的salary总和。

1
2
3
SELECT group_id, job, SUM(salary) FROM group_test
  GROUP BY group_id, job
  WITH ROLLUP;