Oracle ROLLUP 和 CUBE

最新推荐文章于 2024-12-27 10:07:52 发布

原创最新推荐文章于 2024-12-27 10:07:52 发布 · 856 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #plsql #sql

Oracle 专栏收录该内容

125 篇文章

订阅专栏

本文介绍了如何使用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