oracle分析函数（一）

 

主要是GROUPING SETS和ROLLUP函数，具体意思不好说清楚，只能通过例子来学习。

原表数据，如下图

GROUPING SETS测试，

下面两段代码运行结果是一样，

select id,area,stu_type,sum(score) score 
from students
group by grouping sets(id,area,stu_type)


select a.id, null, null, sum( score ) from students a group by a.id
union all
select null, a.area, null, sum( score ) from students a group by a.area 
union all
select null, null, a.stu_type, sum( score ) from students a  group by a.stu_type 

 

 运行结果

ROLLUP测试

两段代码运行结果相同

select id,area,stu_type,sum(score) score 
from students
group by rollup(id,area,stu_type)
-------------------------------------------------------------------
select a.id,a.area,a.stu_type, sum( score ) from students a group by a.id,a.area,a.stu_type
union all
select a.id,a.area, null, sum( score ) from students a group by a.id,a.area
union all
select a.id, null, null, sum( score ) from students a  group by a.id 
union all
select null, null, null, sum( score ) from students a

运行结果