oracle-rollup/cube

今天药忘吃喽~ 2023-10-18 15:58 120阅读 0赞

#### 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);

oracle-rollup/cube

发表评论取消回复

还没有评论，来说两句吧...

相关阅读