在实际开发过程中,对数据进行的查询,常常是不仅要查询数据本身,还要对其进行分组查询。这时候最简单的方法是进行多次查询后将数据合并。但使用grouping可以更方便简洁地进行上述查询。
假设有如下数据表:
查询所有学生的成绩及按班级、性别分组后的平均成绩:
得到如下结果:
[img]http://dl.iteye.com/upload/attachment/0067/7103/acfea5a9-1606-3eb7-a7bb-76a719643a7f.jpg[/img]
可以看到,只要通过一次查询就得到了数据本身和各种分组数据。下面说明用到的特殊语句和函数:
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)相当于如下语句:
假设有如下数据表:
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
得到如下结果:
[img]http://dl.iteye.com/upload/attachment/0067/7103/acfea5a9-1606-3eb7-a7bb-76a719643a7f.jpg[/img]
可以看到,只要通过一次查询就得到了数据本身和各种分组数据。下面说明用到的特殊语句和函数:
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;
}