oracle-rollup/cube-蒲公英云

oracle-rollup/cube

-- 创建一个雇员表
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);
-- 查看数据
SELECT
    * 
FROM
    employee;
-- nvl(col,value) nvl判断如果col列为空，则返回value值，不为空则返回原来的值
-- rollup(col,col)用于计算合计信息
-- cube类似与rollup但是可以统计更多信息
select nvl(country,'总计'),nvl(department,'小计'),round(avg(salary),2)
from employee
group by rollup(country,department);
select nvl(country,'总计'),nvl(department,'小计'),round(avg(salary),2)
from employee
group by cube(country,department);
-- decode 用于类似于if else判断语句
-- grouping 判断该值是否为空，为空则返回1 不为空则返回0
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);
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);