使用grouping进行分组查询，方便得到多种分组结果

在实际开发过程中，对数据进行的查询，常常是不仅要查询数据本身，还要对其进行分组查询。这时候最简单的方法是进行多次查询后将数据合并。但使用grouping可以更方便简洁地进行上述查询。
假设有如下数据表：

create table student(
id varchar2(32) primary key,
class varchar2(32),
name varchar2(32),
sex  varchar2(32),
score integer);
insert into student values('20110101','1','张三','男',90);
insert into student values('20110102','1','李四','男',95);
insert into student values('20110103','1','小明','男',95);
insert into student values('20110104','1','李娜','女',93);
insert into student values('20110105','1','小茜','女',93);
insert into student values('20110201','2','张龙','男',90);
insert into student values('20110202','2','赵虎','男',95);
insert into student values('20110203','2','小倩','女',95);
insert into student values('20110204','2','珊珊','女',94);
insert into student values('20110205','2','小沐','女',94);
commit;
 

查询所有学生的成绩及按班级、性别分组后的平均成绩：

 
select id,
       class,
       decode(grouping_id(class, sex, name),
              7,
              '总平均',
              3,
              class || '班平均',
              1,
              class || '班' || sex || '生平均',
              name) sname,
       sex,
       avg(score)
  from student t
 group by grouping sets((),(class),(class, sex),(class, sex, name, id))
 order by grouping_id(class, sex, name), class, id

得到如下结果：



可以看到，只要通过一次查询就得到了数据本身和各种分组数据。下面说明用到的特殊语句和函数：
1.group by grouping sets((),(class),(class, sex),(class, sex, name, id))
grouping sets 相当与将多个group by的分组条件合并，空的分组条件则是所有数据的总合计。上述语句就相当于查询时没有group条件、使用group by(class)、group by(class,sex)、
group by(class, sex, name, id)这四种情况下查询得到的数据合并到一起。

2.grouping_id(class, sex, name)
grouping_id(arg)函数判断其参数是否参与了分组，如果没有参与则返回1，如果参与了分组则返回0，如在本例中grouping_id(class)在除了总合计以外的结果集中class都参与了分组，即grouping_id(class)在第1~16行返回0，第17行返回1。而其多个参数的形式则将其每个参数进行grouping_id(arg)运算后返回的值拼成二进制后转换为十进制返回，即grouping_id(argn,...,arg2,arg1)=grouping_id(argn)*2^(n-1)+...+grouping_id(arg2)*2^1+grouping_id(arg1)*2^0('^'表示幂运算)。
在上述示例中，在1~10行结果相当于group by(class, sex, name, id)返回的结果，此时grouping_id(class, sex, name)的所有参数参与了分组，则返回值为(000)二进制=(0)十进制；第11~14行相当于group by(class,sex)的结果，此时class和sex参与了分组，grouping_id(class, sex, name)返回值为(001)二进制=(1)十进制；第15、16行相当于group by(class)，此时class参与了分组，grouping_id(class, sex, name)返回值为(011)二进制=(3)十进制；而第17行相当于没有group条件，此时没有分组参数，则grouping_id(class, sex, name)返回值为(111)二进制=(7)十进制。

3.decode(grouping_id(class, sex, name),7, '总平均',3,class || '班平均',1,class || '班' || sex || '生平均',name)
decode(condition,arg1,return1,arg2,return2,...,argn,returnn,default)相当于如下语句：

 
if(condition = arg1) {
    return return1;
}else if(condition = arg2) {
    return return2;
}
 ......
else if(condition = argn) {
    return returnn;
}else {
    return defualt;
}