Oracle ROLLUP 和 CUBE

本文介绍了如何使用SQL中的GROUP BY ROLLUP和GROUP BY CUBE进行数据分组及汇总,通过具体示例展示了如何计算不同维度的小计和总计,并美化输出结果。

-- Start

不知道大家听没听说过小计,总计等会计词语,如果你做过报表,一定不会陌生。

CREATE TABLE employee  
(  
  name          NVARCHAR2(10),  
  gender        NCHAR(1),  
  country       NVARCHAR2(10),  
  department    NVARCHAR2(10),  
  salary        NUMBER(10)  
);  
INSERT INTO employee VALUES ('张三','男','中国','市场部',4000);  
INSERT INTO employee VALUES ('李四','男','中国','市场部',5000);  
INSERT INTO employee VALUES ('王五','女','美国','市场部',3000);    
INSERT INTO employee VALUES ('赵红','男','中国','技术部',2000);  
INSERT INTO employee VALUES ('李白','女','中国','技术部',5000);    
INSERT INTO employee VALUES ('王蓝','男','美国','技术部',4000);

通常我们是在生成报表时算小计或总计的,其实通过 SQL 就可以实现。

SELECT 
  country, 
  department, 
  round(avg(salary), 2) AVG
FROM 
  employee 
GROUP BY ROLLUP(country, department);


结果如下:
country department AVG
中国	市场部	4500
中国	技术部	3500
中国	null	4000
美国	市场部	3000
美国	技术部	4000
美国	null	3500
null	null	3833.33

上面的 null 是不是让人看着很不爽,要是能返回有意义的值就更好了,为此 Oralce 提供几个函数实现这个功能。

SELECT 
  DECODE(GROUPING(country), 1, '总计', country) AS country,
  DECODE(GROUPING(department), 1, '小计', department) AS department,
  round(avg(salary), 2) AVG
FROM 
  employee 
GROUP BY ROLLUP(country, department);


结果如下:
country department AVG
中国	市场部	4500
中国	技术部	3500
中国	小计	4000
美国	市场部	3000
美国	技术部	4000
美国	小计	3500
总计	小计	3833.33

还有个 CUBE 关键字,它比 ROLLUP 语句返回更多的内容,以下是将上面语句的 ROLLUP 替换为 CUBE 后得到的结果:

SELECT 
  DECODE(GROUPING(country), 1, '总计', country) AS country,
  DECODE(GROUPING(department), 1, '小计', department) AS department,
  round(avg(salary), 2) AVG
FROM 
  employee 
GROUP BY CUBE(country, department);


结果如下:
country department AVG
总计	小计	3833.33
总计	市场部	4000
总计	技术部	3666.67
中国	小计	4000
中国	市场部	4500
中国	技术部	3500
美国	小计	3500
美国	市场部	3000
美国	技术部	4000

--更多参见:Oracle SQL 精萃

-- 声明:转载请注明出处

-- Last edited on 2015-02-28

-- Created by ShangBo on 2015-02-28

-- End


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值