Oracle聚合函数和其扩展使用.doc

资源描述

数据源 Framework Report Studio Manager 未指定，或 none 不支持无 average count avg () 平均值计数平均值计数 count distinct 计数差异计数差异 maximum () max 最大值最大值 minimum median min () 最小值中间值最小值中间值 standard deviation sum stddev) 标准偏差 ( 求和标准偏差合计 variance var () 差量差量 count non zero average non zero external any calculated unknown first_period last_period 计数非零未知未知未知未知未知未知未知自动自动自动自动自动自动自动自动以下汇总函数仅存在于 Framework Report 中。在数据源中没有对应的汇总 Manager 或 Studio 函数。 Framework Report Studio Manager 自动自动 Framework Report Studio Manager 已计算已计算无对应的汇总函数汇总无对应的汇总函数不适用聚合函数的扩展使用 1.ROLLUP 子句 ROLLUP 是 GROUP BY 子句的扩展，它是为每一个分组返回一条合计记录，并为全部分组返回总计。下面举例说明： EG： select * from employees2; EMPLOYEE_ID DIVISI JOB_ID FIRST_NAME LAST_NAME SALARY ----------- ------ ------ -------------------- -------------------- ---------- 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 11 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 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 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 employees2 group by division_id; DIVISI SUM(SALARY) ------ ----------- BUS 1610000 OPE 1320000 SAL 4936000 SUP 1015000 接下来是扩展了的 ROLLUP 的使用： select division_id,SUM(salary) from employees2 group by ROLLUP(division_id); DIVISI SUM(SALARY) ------ ----------- BUS OPE SAL SUP 1610000 1320000 4936000 1015000 8881000 再来看个例子： select division_id,job_id,SUM(salary) from employees2 group by ROLLUP(division_id,job_id); DIVISI JOB_ID SUM(SALARY) ------ ------ ----------- BUS BUS BUS BUS OPE MGR PRE WOR ENG 530000 800000 280000 1610000 245000

展开阅读全文