《oracle聚合函数及其扩展使用》由会员分享，可在线阅读，更多相关《oracle聚合函数及其扩展使用（8页珍藏版）》请在金锄头文库上搜索。
1、数据源Framework ManagerReport Studio未指定，或 none不支持无average (avg)平均值平均值count计数计数count distinct计数差异计数差异maximum (max)最大值最大值minimum (min)最小值最小值median中间值中间值standard deviation (stddev)标准偏差标准偏差sum求和合计variance (var)差量差量count non zero计数非零自动average non zero未知自动external未知自动any未知自动calculated未知自动unknown未知自动first_per
2、iod未知自动last_period未知自动以下汇总函数仅存在于 Framework Manager 或 Report Studio 中。在数据源中没有对应的汇总函数。Framework ManagerReport Studio自动自动已计算已计算无对应的汇总函数汇总无对应的汇总函数不适用 聚合函数的扩展使用1.ROLLUP子句 ROLLUP是GROUP BY子句的扩展，它是为每一个分组返回一条合计记录，并为全部分组返回总计。下面举例说明：EG：select * from employees2;EMPLOYEE_ID DIVISI JOB_ID FIRST_NAME LAST_NAME SAL
3、ARY- - - - - - 1 BUS PRE James Smith 800000 2 SAL MGR Ron Johnson 350000 3 SAL WOR Fred Hobbs 140000 4 SUP MGR Susan Jones 200000 5 SAL WOR Rob Green 350000 6 SUP WOR Jane Brown 200000 7 SUP MGR John Grey 265000 8 SUP WOR Jean Blue 110000 9 SUP WOR Henry Heyson 125000 10 OPE MGR Kevin Black 225000 1
4、1 OPE MGR Keith Long 165000 12 OPE WOR Frank Howard 125000 13 OPE WOR Doreen Penn 145000 14 BUS MGR Mark Smith 155000 15 BUS MGR Jill Jones 175000 16 OPE ENG Megan Craig 245000 17 SUP TEC Matthew Brant 115000 18 OPE MGR Tony Clerke 200000 19 BUS MGR Tanya Conway 200000 20 OPE MGR Terry Cliff 215000
5、21 SAL MGR Steve Green 275000 22 SAL MGR Roy Red 375000 23 SAL MGR Sandra Smith 335000 24 SAL MGR Gail Silver 225000 25 SAL MGR Gerald Gold 245000 26 SAL MGR Eileen Lane 235000 27 SAL MGR Doreen Upton 235000 28 SAL MGR Jack Ewing 235000 29 SAL MGR Paul Owens 245000 30 SAL MGR Melanie York 255000 31
6、SAL MGR Tracy Yellow 225000 32 SAL MGR Sarah White 235000 33 SAL MGR Terry Iron 225000 34 SAL MGR Christine Brown 247000 35 SAL MGR John Brown 249000 36 SAL MGR Kelvin Trenton 255000 37 BUS WOR Damon Jones 280000已选择37行。首先我们来看单独的GROUP BY语句：select division_id,SUM(salary) from employees2group by divisi
7、on_id;DIVISI SUM(SALARY)- -BUS 1610000OPE 1320000SAL 4936000SUP 1015000接下来是扩展了的ROLLUP的使用：select division_id,SUM(salary) from employees2group by ROLLUP(division_id);DIVISI SUM(SALARY)- -BUS 1610000OPE 1320000SAL 4936000SUP 1015000 8881000再来看个例子：select division_id,job_id,SUM(salary) from employees2 gr
8、oup by ROLLUP(division_id,job_id);DIVISI JOB_ID SUM(SALARY)- - -BUS MGR 530000BUS PRE 800000BUS WOR 280000BUS 1610000OPE ENG 245000OPE MGR 805000OPE WOR 270000OPE 1320000SAL MGR 4446000SAL WOR 490000SAL 4936000SUP MGR 465000SUP TEC 115000SUP WOR 435000SUP 1015000 8881000小结：扩展了一个rollup子句带来了分组的一个合计。2.CUBE子句CUBE子句也是对GROUP BY子句进行扩展，返回CUBE中所有列组合的小计信息，同时在最后显示总计信息。EG：select
