Oracle分组函数之ROLLUP

功能介绍：

首先是进行无字段的聚合,然后在对字段进行从左到右依次组合后聚合

创建表：

 Create Table score

 (

   classID Int,

   studentName Varchar2(50),

   subject varchar2(50),

   score Int

 );

插入测试数据：

 Insert Into score values (001,'小徐','语文',87);

 Insert Into score values (001,'小徐','数学',98);

 Insert Into score values (001,'小徐','外语',99);

 Insert Into score values (002,'小吴','语文',80);

 Insert Into score values (002,'小吴','数学',74);

 Insert Into score values (002,'小吴','外语',65);

 Insert Into score values (003,'小张','语文',89);

 Insert Into score values (003,'小张','数学',78);

 Insert Into score values (003,'小张','外语',84);

 Insert Into score values (004,'小孙','语文',100);

 Insert Into score values (004,'小孙','数学',100);

 Insert Into score values (004,'小孙','外语',100);

 Insert Into score values (001,'小彭','语文',87);

 Insert Into score values (001,'小彭','数学',99);

 Insert Into score values (001,'小彭','外语',65);

 Insert Into score values (004,'小叶','语文',100);

 Insert Into score values (004,'小叶','数学',100);

 Insert Into score values (004,'小叶','外语',100);

 Insert Into score values (003,'小刘','语文',79);

 Insert Into score values (003,'小刘','数学',90);

 Insert Into score values (003,'小刘','外语',65);

 Insert Into score values (002,'小童','语文',96);

 Insert Into score values (002,'小童','数学',93);

 Insert Into score values (002,'小童','外语',97);

普通分组函数，统计每个班级的总分：

Select t.Classid, Sum(t.Score) From Score t Group By t.Classid;

查询结果：

加上Rollup，统计每个班级的总分和所有班级的总分：

Select t.Classid, Sum(t.Score) From Score t Group By Rollup(t.Classid);

查询结果：

先进行无字段的聚合（1），再对Classid聚合（3），相当于：

 Select Null, Sum(t.Score) From Score t

 Union All

 Select t.Classid, Sum(t.Score) From Score t Group By t.Classid;

在看看两个字段的，统计每个班级的总分、所有班级的总分和每个学生的总成绩：

Select t.classid,t.studentname,Sum(t.score) From Score t Group By Rollup(t.classid,t.studentname); 

查询结果：

先进行无字段的聚合（1），再对Classid聚合（3），在对Classid和Studentname组合聚合，相当于：

 Select Null, Null, Sum(t.Score) From Score t

 Union All

 Select t.Classid, Null, Sum(t.Score) From Score t Group By t.Classid

 Union All

 Select t.Classid, t.Studentname, Sum(t.Score) From Score t Group By t.Classid, t.Studentname