ROLLUP,是GROUP BY子句的一种扩展,可以为每个分组返回小计记录以及为所有分组返回总计记录。
CUBE,也是GROUP BY子句的一种扩展,可以返回每一个列组合的小计记录,同时在末尾加上总计记录。
在文章的最后附上了相关表和记录创建的脚本。
1、向ROLLUP传递一列
SQL> select division_id,sum(salary)
2 from employees2
3 group by rollup(division_id)
4 order by division_id;
DIV SUM(SALARY)
--- -----------
BUS 1610000
OPE 1320000
SAL 4936000
SUP 1015000
8881000
SQL>
再来看一下如果使用普通的GROUP BY,而没有ROLLUP是个什么情况
SQL> select division_id,sum(salary)
2 from employees2
3 group by division_id
4 order by division_id;
DIV SUM(SALARY)
--- -----------
BUS 1610000
OPE 1320000
SAL 4936000
SUP 1015000
可以看到,缺少了最后的统计信息。
2、向ROLLUP传递多列
SQL> select division_id,job_id,sum(salary)
2 from employees2
3 group by rollup(division_id,job_id)
4 order by division_id,job_id;
DIV JOB SUM(SALARY)
--- --- -----------
BUS MGR 530000
BUS PRE 800000
BUS WOR 280000
BUS 1610000
OPE ENG 245000
OPE MGR 805000
OPE WOR 270000
OPE 1320000
SAL MGR 4446000
SAL WOR 490000
SAL 4936000
DIV JOB SUM(SALARY)
--- --- -----------
SUP MGR 465000
SUP TEC 115000
SUP WOR 435000
SUP 1015000
8881000
16 rows selected.
可以看到,除了在最后有一个求和记录外,每个division_id分组也会有一个求和记录。
那么我们现在交换一下ROLLUP中数据列的顺序,看看结果怎样
SQL> select job_id,division_id,sum(salary)
2 from employees2
3 group by rollup(job_id,division_id)
4 order by job_id,division_id;
JOB DIV SUM(SALARY)
--- --- -----------
ENG OPE 245000
ENG 245000
MGR BUS 530000
MGR OPE 805000
MGR SAL 4446000
MGR SUP 465000
MGR 6246000
PRE BUS 800000
PRE 800000
TEC SUP 115000
TEC 115000
JOB DIV SUM(SALARY)
--- --- -----------
WOR BUS 280000
WOR OPE 270000
WOR SAL 490000
WOR SUP 435000
WOR 1475000
8881000
17 rows selected.
结果类似,只不过是每一个job_id分组有一个求和记录罢了。
3、向CUBE传递一列
SQL> select division_id,sum(salary)
2 from employees2
3 group by cube(division_id)
4 order by division_id;
DIV SUM(SALARY)
--- -----------
BUS 1610000
OPE 1320000
SAL 4936000
SUP 1015000
8881000
好像和ROLLUP没什么区别哦,呵呵,继续往下看。
4、向CUBE传递多列
SQL> select job_id,division_id,sum(salary)
2 from employees2
3 group by cube(job_id,division_id)
4 order by job_id,division_id;
JOB DIV SUM(SALARY)
--- --- -----------
ENG OPE 245000
ENG 245000
MGR BUS 530000
MGR OPE 805000
MGR SAL 4446000
MGR SUP 465000
MGR 6246000
PRE BUS 800000
PRE 800000
TEC SUP 115000
TEC 115000
JOB DIV SUM(SALARY)
--- --- -----------
WOR BUS 280000
WOR OPE 270000
WOR SAL 490000
WOR SUP 435000
WOR 1475000
BUS 1610000
OPE 1320000
SAL 4936000
SUP 1015000
8881000
21 rows selected.
可以看到工资是根据job_id和division_id求和的,CUBE在每一个job_id中都返回一条记录,表示其中的工资总数,同时在接近末尾处显示每一种division_id的工资总数,最后一条记录显示所有工资的总数。
把两列的顺序换换会怎样?呵呵,真的有兴趣那就自己动手试试吧。
==================================================================================
CREATE TABLE divisions (
division_id CHAR(3) CONSTRAINT divisions_pk PRIMARY KEY,
name VARCHAR2(15) NOT NULL
);
CREATE TABLE jobs (
job_id CHAR(3) CONSTRAINT jobs_pk PRIMARY KEY,
name VARCHAR2(20) NOT NULL
);
CREATE TABLE employees2 (
employee_id INTEGER CONSTRAINT employees2_pk PRIMARY KEY,
division_id CHAR(3)
CONSTRAINT employees2_fk_divisions
REFERENCES divisions(division_id),
job_id CHAR(3) REFERENCES jobs(job_id),
first_name VARCHAR2(10) NOT NULL,
last_name VARCHAR2(10) NOT NULL,
salary NUMBER(6, 0)
);
INSERT INTO divisions (
division_id, name
) VALUES (
'SAL', 'Sales'
);
INSERT INTO divisions (
division_id, name
) VALUES (
'OPE', 'Operations'
);
INSERT INTO divisions (
division_id, name
) VALUES (
'SUP', 'Support'
);
INSERT INTO divisions (
division_id, name
) VALUES (
'BUS', 'Business'
);
INSERT INTO jobs (
job_id, name
) VALUES (
'WOR', 'Worker'
);
INSERT INTO jobs (
job_id, name
) VALUES (
'MGR', 'Manager'
);
INSERT INTO jobs (
job_id, name
) VALUES (
'ENG', 'Engineer'
);
INSERT INTO jobs (
job_id, name
) VALUES (
'TEC', 'Technologist'
);
INSERT INTO jobs (
job_id, name
) VALUES (
'PRE', 'President'
);
INSERT INTO employees2 (
employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
1, 'BUS', 'PRE', 'James', 'Smith', 800000
);
INSERT INTO employees2 (
employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
2, 'SAL', 'MGR', 'Ron', 'Johnson', 350000
);
INSERT INTO employees2 (
employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
3, 'SAL', 'WOR', 'Fred', 'Hobbs', 140000
);
INSERT INTO employees2 (
employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
4, 'SUP', 'MGR', 'Susan', 'Jones', 200000
);
INSERT INTO employees2 (
employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
5, 'SAL', 'WOR', 'Rob', 'Green', 350000
);
INSERT INTO employees2 (
employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
6, 'SUP', 'WOR', 'Jane', 'Brown', 200000
);
INSERT INTO employees2 (
employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
7, 'SUP', 'MGR', 'John', 'Grey', 265000
);
INSERT INTO employees2 (
employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
8, 'SUP', 'WOR', 'Jean', 'Blue', 110000
);
INSERT INTO employees2 (
employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
9, 'SUP', 'WOR', 'Henry', 'Heyson', 125000
);
INSERT INTO employees2 (
employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
10, 'OPE', 'MGR', 'Kevin', 'Black', 225000
);
INSERT INTO employees2 (
employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
11, 'OPE', 'MGR', 'Keith', 'Long', 165000
);
INSERT INTO employees2 (
employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
12, 'OPE', 'WOR', 'Frank', 'Howard', 125000
);
INSERT INTO employees2 (
employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
13, 'OPE', 'WOR', 'Doreen', 'Penn', 145000
);
INSERT INTO employees2 (
employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
14, 'BUS', 'MGR', 'Mark', 'Smith', 155000
);
INSERT INTO employees2 (
employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
15, 'BUS', 'MGR', 'Jill', 'Jones', 175000
);
INSERT INTO employees2 (
employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
16, 'OPE', 'ENG', 'Megan', 'Craig', 245000
);
INSERT INTO employees2 (
employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
17, 'SUP', 'TEC', 'Matthew', 'Brant', 115000
);
INSERT INTO employees2 (
employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
18, 'OPE', 'MGR', 'Tony', 'Clerke', 200000
);
INSERT INTO employees2 (
employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
19, 'BUS', 'MGR', 'Tanya', 'Conway', 200000
);
INSERT INTO employees2 (
employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
20, 'OPE', 'MGR', 'Terry', 'Cliff', 215000
);
INSERT INTO employees2 (
employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
21, 'SAL', 'MGR', 'Steve', 'Green', 275000
);
INSERT INTO employees2 (
employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
22, 'SAL', 'MGR', 'Roy', 'Red', 375000
);
INSERT INTO employees2 (
employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
23, 'SAL', 'MGR', 'Sandra', 'Smith', 335000
);
INSERT INTO employees2 (
employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
24, 'SAL', 'MGR', 'Gail', 'Silver', 225000
);
INSERT INTO employees2 (
employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
25, 'SAL', 'MGR', 'Gerald', 'Gold', 245000
);
INSERT INTO employees2 (
employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
26, 'SAL', 'MGR', 'Eileen', 'Lane', 235000
);
INSERT INTO employees2 (
employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
27, 'SAL', 'MGR', 'Doreen', 'Upton', 235000
);
INSERT INTO employees2 (
employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
28, 'SAL', 'MGR', 'Jack', 'Ewing', 235000
);
INSERT INTO employees2 (
employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
29, 'SAL', 'MGR', 'Paul', 'Owens', 245000
);
INSERT INTO employees2 (
employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
30, 'SAL', 'MGR', 'Melanie', 'York', 255000
);
INSERT INTO employees2 (
employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
31, 'SAL', 'MGR', 'Tracy', 'Yellow', 225000
);
INSERT INTO employees2 (
employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
32, 'SAL', 'MGR', 'Sarah', 'White', 235000
);
INSERT INTO employees2 (
employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
33, 'SAL', 'MGR', 'Terry', 'Iron', 225000
);
INSERT INTO employees2 (
employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
34, 'SAL', 'MGR', 'Christine', 'Brown', 247000
);
INSERT INTO employees2 (
employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
35, 'SAL', 'MGR', 'John', 'Brown', 249000
);
INSERT INTO employees2 (
employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
36, 'SAL', 'MGR', 'Kelvin', 'Trenton', 255000
);
INSERT INTO employees2 (
employee_id, division_id, job_id, first_name, last_name, salary
) VALUES (
37, 'BUS', 'WOR', 'Damon', 'Jones', 280000
);
相关推荐
NULL 博文链接:https://dolphin-ygj.iteye.com/blog/416356
oracle rollup及cube的使用,开发人员必备的资料
oracle 聚合函数的扩展使用 1.ROLLUP子句 ROLLUP是GROUP BY子句的扩展,它是为每一个分组返回一条合计记录,并为全部分组返回总计。 2.CUBE子句也是对GROUP BY子句进行扩展,返回CUBE中所有列组合的小计信息,...
NULL 博文链接:https://wangjingyi.iteye.com/blog/1545090
关于oracle的所有分析函数的使用,像rollup,cube之类的.
GROUP BY with ROLLUP and CUBE Operators 3-6 ROLLUP Operator 3-7 ROLLUP Operator Example 3-8 CUBE Operator 3-9 CUBE Operator: Example 3-10 GROUPING Function 3-11 GROUPING Function: Example 3-12 ...
Oracle 分析函数详解 1. 自动汇总函数rollup,cube, 2. rank 函数, rank,dense_rank,row_number 3. lag,lead函数 4. sum,avg,的移动增加,移动平均数 5. ratio_to_report报表处理函数 6. first,last取基数的分析函数
14.3 使用ROLLUP、GROUPING和CUBE 14.4 家族树和COlLrlectby 14.4.1 排除个体和分支 14.4.2 向根遍历 14.4.3 基本规则 第15章 更改数据:插入、更新、合并和删除 第16章 DECODE和CASE.SQL中的if-fhen-else 第17章 ...
从论坛搜到的,感觉还...1. 自动汇总函数rollup,cube, 2. rank 函数, rank,dense_rank,row_number 3. lag,lead函数 4. sum,avg,的移动增加,移动平均数 5. ratio_to_report报表处理函数 6. first,last取基数的分析函数
14.3 使用ROLLUP、GROUPING和CUBE 14.4 家族树和COlLrlectby 14.4.1 排除个体和分支 14.4.2 向根遍历 14.4.3 基本规则 第15章 更改数据:插入、更新、合并和删除 第16章 DECODE和CASE.SQL中的if-fhen-else 第17章 ...
有近20年使用Oracle技术产品以及Oracle数据库管理员/Oracle数据库应用管理员的经验,是真正应用集群、性能调优以及数据库内部属性方面的专家。同时是一位演讲家及Oracle ACE。 JARED STILL 从1994年就开始使用...
使用SQL语句 4.1 使用基本查询 4.1.1 简单查询语句 4.1.2 ...使用事务控制语句 4.3.1 事务和锁 4.3.2 提交事务 4.3.3 回退事务 4.3.4 只读事务 4.3.5 顺序事务 4.4 数据...
实验28:高级分组rollup,cube操作 65 实验29:树结构的查询start with子句 66 实验30:高级dml操作 68 第二部分pl/sql基础 69 匿名块的编写 69 实验31:书写一个最简单的块,运行并查看结果 69 实验32:在块中操作...
·1Oracle分析函数一——函数列表 ·2Oracle分析函数二——函数用法 ·3Oracle分析函数三——SUM,AVG,MIN,MAX,COUNT ·4Oracle分析函数四——函数RANK,DENSE_RANK,FIRST,LAST...·8Oracle分析函数八——CUBE,ROLLUP
8.3.3 ROLLUP运算符和CUBE运算符 8.3.4 GROUP BY子句中的NULL值处理 8.3.5 HAVING子句 8.3.6 HAVING子句与WHERE子句 8.3.7 SELECT语句各查询子句总结 第9章 多表查询 9.1 本章用到的实例表 ...
20.oracle8中扩充了group by rollup和cube的操作。有时候省了你好多功夫的。 下面的语句可以进行总计 select region_code,count(*) from aicbs.acc_woff_notify group by rollup(region_code); <2> 对第1个字段...
8.3.3 rollup运算符和cube运算符 151 8.3.4 group by子句中的null值处理 153 8.3.5 having子句 153 8.3.6 having子句与where子句 154 8.3.7 select语句各查询子句总结 156 第9章 多表查询 157 9.1 本章用到的...
组合集总计: group by with rollup/cube grouping sets 子查询按执行方式分:标准子查询、关联子查询 标准子查询:嵌套子查询 标量值查询 case with then 使用exits查询 select user_id,user_name from tb_001 tb ...
详细介绍了cube、rollup、grouping sets的原理及用法