Oracle 高级查询

create table tt as select * from emp;
alter table tt add (sex varchar2(1) default '0' );
select * from tt for update;


-- 职位分别是’MANAGER’、’ANALYST’、’SALESMAN’时，奖励金额分别是薪水的1.2倍、1.1倍、1.05倍，如果不是这三个职位，则奖励金额取薪水值：

select  e.empno , e.ename , e.job ,
        decode(
                e.job , 'MANAGER' ,e.sal*1.2,
                      'ANALYST' , e.sal*1.1,
                      'SALESMAN' ,e.sal*1.05,
                      e.sal
        ) bouns 
 from emp e ;
 
 select decode(e.job , 'MANAGER' ,'vip',
                                 'ANALYST' , 'vip',
                                 'OPERATION' ) job  , count(1) countJob from emp e group by decode(e.job , 'MANAGER' ,'vip','ANALYST' , 'vip', 'OPERATION' );
-- 查询tt表的男女多少(sex 列有可能为空，不知道男女)?  1 :男 , 0 :女
select count(*) as "性别" from tt where sex='0' union all  select count(*) as "性别" from tt where sex='1';
--decode 实现
select decode(t.sex , '1' , '男'  , '女')  from tt t ;
                       
SELECT deptno, dname, loc
FROM dept
ORDER BY 
      DECODE(dname, '研发部',1,'市场部',2,'销售部',3), loc;

-- 排序函数 : 
-- row_number() over (partition by c1  order by c2 )       
-- 表示根据c1分组，在分组内部根据c2排序。此函数计算的值就表示每组内部排序后的顺序编号，组内连续且唯一
SELECT deptno, ename, empno,
       ROW_NUMBER()
               OVER (PARTITION BY deptno ORDER BY sal)     AS  empSal
   FROM emp;      

-- RANK : rank()  over(partition by  c1 order by c2 )    
--表示根据c1分组，在分组内部根据c2排名，相同的数据返回相同排名。
-- 特点是跳跃排序，如果有相同数据，则排名相同，比如并列第二，则两行数据都标记为2，但下一位将是第四名。 
 -- 注意: row_number 结果没有重复,  rank(); 有重复
 select e.*  , rank() over(partition by e.deptno order by e.sal ) as "e.sal"  from emp e;
 
-- DENSE_RANK:  dense_rank() over ( partition by c1 order by c2 )
--表示根据c1分组，在分组内部根据c2排名，相同的数据返回相同排名。
-- 特点是排序不跳跃，如果有相同数据，则排名相同，比如并列第二，则两行数据都标记为2，但下一位将是第3名。 
 -- 注意: row_number 结果没有重复（相同的数据也没有重复）,  rank(); 有重复
 -- rank() 排名是跳跃的，dense_rank(); 拍名是连续的
 select e.* , dense_rank() over(partition by e.deptno order by e.sal ) as "e.sal" from emp e;
 
 
 -- 高级分组函数:是 GROUP BY 子句的扩展 (简化union all 的结果,比union all的效率高)
 --GROUP BY ROLLUP(a, b, c)          rollup 汇总
-- GROUP BY CUBE(a, b, c)            cube 立方
-- GROUP BY GROUPING SETS ( (a), (b))      

--SELECT a,b,c,SUM(d) FROM test GROUP BY ROLLUP(a,b,c);
-- 等价于: (group by a, b ,c)  union all  (group by a, b)  union all (group by a ) union all  (select sum(d) from test); 
-- 对ROLLUP的列从右到左以一次少一列的方式进行分组直到所有列都去掉后的分组(也就是全表分组)。
-- 对于n个参数的ROLLUP，有n+1次分组。

-- GROUP BY CUBE(a, b, c)
-- (group by a, b ,c)  union all  (group by a, b )    union all  (group by a, c)  union all  (group by a )  union all  (group by b ,c ) 
 -- union all  (group by b)  union all  (group by c )   union all  (select sum(d) from test); 
 --对cube的每个参数，都可以理解为取值为参与分组和不参与分组两个值的一个维度，
 -- 所有维度取值组合的集合就是分组后的集合。对于n个参数的cube，有2^n次分组。
    
 -- grouping sets
 -- 如果不需要获得由完备的 ROLLUP 或 CUBE 运算符生成的全部分组，
 -- 则可以使用 GROUPING SETS 仅指定所需的分组。GROUPING SETS 列表可以包含重复的分组。 
 --使用GROUP BY GROUPING SETS(a,b,c)，则对(a),(b),(c)进行GROUP BY
 --使用GROUP BY GROUPING SETS((a,b),c), 则对(a,b),(c)进行GROUP BY
 --GROUPING BY GROUPING SET(a,a) , 则对(a)进行2次GROUP BY, GROUPING SETS的参数允许重复