oracle decode集排序,十三、Oracle学习笔记：decode函数、排序函数和高级分组函数...

一、decode 函数

(1)用法：decode(colName,search1,result1,search2,result2.....,[default])

(2)逻辑：检索colName的值，如果是search1结果就是result1，同理往后....否则使用default，没有default时，返回的是null

--练习:年终发奖金，职位'MANAGER'发放月薪的1.2倍,职位'SALESMAN'发放月薪的1.1倍,

-- 职位'CLERK'发放月薪的1.05倍,其他职位发放月薪作为奖金，查询员工的姓名，职位，年终奖。

select ename,job,sal,decode(job,'MANAGER',sal*1.2,'SALESMAN',sal*1.1,'CLERK',sal*1.05,sal) 年终奖 from emp;

(3)decode与group by连用

--练习: 职位'MANAGER','SALESMAN'是VIP,'CLERK'SVIP,其他 IP

select ename,job,decode(job,'MANAGER','SVIP','SALESMAN','SVIP','CLERK','VIP','IP') from emp;

--练习:查询这三种级别的人数

select job,decode(job,'MANAGER','SVIP','SALESMAN','SVIP','CLERK','VIP','IP') 级别,count(*) 级别人数

from emp group by job;

or

select decode(job,'MANAGER','SVIP','SALESMAN','SVIP','CLERK','VIP','IP'),count(*) 级别人数

from emp group by decode(job,'MANAGER','SVIP','SALESMAN','SVIP','CLERK','VIP','IP');

(4)decode与order by连用

--练习:部门中有4个部门'ACCOUNTING','RESEARCH','SALES','OPERATIONS'

--练习:按照部门名称排序，查询部门信息

select * from dept order by dname desc;

--练习:排序规则'ACCOUNTING','SALES','RESEARCH','OPERATIONS'依次排序

select deptno,dname,loc,decode(dname,'ACCOUNTING',1,'SALES',2,'RESEARCH',3,'OPERATIONS',4)

from dept order by decode(dname,'ACCOUNTING',1,'SALES',2,'RESEARCH',3,'OPERATIONS',4);

二、case

与decode函数功能一样的：

--格式:case colName when search1 then result1

--                  when search2 then result2

--                    .......

--                  else result0 end

--练习:

select ename,job,sal,case job when 'MANAGER' then sal*1.2

when 'SALESMAN' then sal*1.1

when 'CLERK' then sal*1.05

else sal end 年终奖 from emp;

三、排序函数：(写在select子句中)

举例：假设现在又三名同学成绩排序，各个函数的排序规则如下：

1: row_number() over(partition by colName1 order by colName2); 连续不重复   第一名 第二名 第三名

2: rank() over(partition by colName1 order by colName2);               重复不连续   第一名 第一名 第三名

3: rank_dense()  over(partition by colName1 order by colName2);  连续重复     第一名 第一名 第二名

排序函数可以直接进行区间查询

partition by colName1:表示按照colName1分组(可以省略)

order by colName2:表示按照colName2排序

--练习:查询所有员工信息，按照月薪排序

select e.*,row_number() over(order by sal) rn from emp e;

select e.*,rank() over(order by sal) rn from emp e;

select e.*,rank_dense() over(order by sal) rn from emp e;

--练习:查看每个部门的员工信息，按照月薪排名

select e.*,row_number() over(partition by deptno order by sal) rn from emp e;

select e.*,rank() over(partition by deptno order by sal) rn from emp e;

select e.*,rank_dense() over(partition by deptno order by sal) rn from emp e;

--练习：区间查询

select * from (select e.*,row_number() over(order by sal) rn from emp e)

where rn between 1 and 3;

四、高级分组函数

rollup(p1,p2,p3)

参数n个，共分n+1种情况，先按照所有参数分组 先按所有参数分一种情况，然后往后一次少一个参数进行分组

--练习：先按照p1,p2,p3分组，再按照p1,p2分组，再按p1分组，最后整张表为一组

select year_id,month_id,day_id,sum(sales_value) from salestable group by rollup(year_id,month_id,day_id);

cube(p1,p2,p3)

参数有n个，组合有2^n种，p1,p2,p3,p1p2,p1p3,p2p3,p1p2p3,null中;

select year_id,month_id,day_id,sum(sales_value) from salestable group by cube(year_id,month_id,day_id);

grouping sets(p1,p2,p3)

n个参数，组合有n个，每个参数为一种情况，可以自定义分组

--练习:统计每年，月，日的销售额

select year_id,month_id,day_id,sum(sales_value) from salestable

group by grouping sets(year_id,(year_id,month_id),(year_id,month_th,day_id));

--上述例子有45种，3+12+30

--练习:统计每年，每月，每日

select year_id,month_id,day_id,sum(sales_value) from salestable

group by grouping sets(year_id,month_id,day_id);