Oracle基础(十三):DECODE()函数、CASE...WHEN...语句、排序函数

一、DECODE()函数

DECODE(expr, search1, result1[,search2,result2...][,default])

DECODE函数,可以实现分支效果(类似于java的swith...case)。(必须至少传三个参数),

根据第一个参数的值来匹配search来执行result,不匹配则取default,否则返回NULL

参数说明:

①该函数用于比较expr的值,如果匹配到哪个search条件,就返回对应的result结果。
②可以有多组searchresult的对应关系,如果都没匹配到,则返回default的值。
default参数是可选的,若未提供default的参数值,当没有匹配到时,返回NULL

--根据指定职位涨工资,如果不是这些职位则取default值
select ename,job,sal,
decode(job,
'MANAGER',sal*1.2,
'ANALYST',sal*1.1,
'SALESMAN',sal*1.05,
sal) bonus
from emp;
--不指定default则返回NULL
select ename,job,sal,
decode(job,
'MANAGER',sal*1.2,
'ANALYST',sal*1.1,
'SALESMAN',sal*1.05) bonus
from emp;

二、CASE...WHEN...语句

DECODE函数功能类似的有CASE语句,实现类似于if..else
若不匹配任何一个WHEN,也不指定ELSE的值时返回NULL语法:

--语法
SELECT xxx,xxx,CASE expr
WHEN expr THEN result1
WHEN expr WHEN result1
...
ELSE default END [alias]
FROM XXX;
--根据指定职位涨工资(使用CASE语句)
select ename,job,sal,CASE job
WHEN 'MANAGER' THEN sal*1.2
WHEN 'ANALYST' THEN sal*1.1
WHEN 'SALESMAN' THEN sal*1.05
ELSE sal END bonus
from emp;

三、DECODE函数在GROUP BY分组中的应用 

DECODE函数在GROUP BY分组中的应用可以将字段值不同的记录看作一组。

 也就是通过使用DECODE函数将值不相同的字段替换成相同的值,然后使用GROUP BY依据DECODE函数的值来分组。

--将职位是MANAGER和ANALYST看作一组
select ename,job,
decode(job,
'MANAGER','VIP',
'ANALYST','VIP',
'OTHER')
from emp;
--统计人数,将职位是MANAGER和ANALYST看作一组(VIP),其余职业看做另一组(OTHER)分别统计人数。
select count(*),
decode(job,
'MANAGER','VIP',
'ANALYST','VIP',
'OTHER')
from emp group by decode(job,
'MANAGER','VIP',
'ANALYST','VIP',
'OTHER');

 

使用DECODE函数在GROUP BY分组中的应用 ,实现自定义排序:

使用DECOED函数将指定字段的值替换成其它方便排序的值。

按字段内容排序:场景:

 dept表按部门名称“OPERATIONS”、“ACCOUNTING”、“SALES”排序,无法按照字面数据排序。
使用DECODE函数自定义排序规则,预期结果应为O、A、S、R,(未指定default是值为NULL,NULL为最大值)

SELECT deptno,dname,loc,decode(dname,'OPERATIONS',1,'ACCOUNTING',2,'SALES',3)
FROM dept ORDER BY
decode(dname,'OPERATIONS',1,'ACCOUNTING',2,'SALES',3);--自定义排序

四、排序函数

排序函数允许将结果集按照指定字段分组,在分组内再按照指定字段排序,然后该函数在组内生成一个行号。

(1)ROW_NUMBER()函数

ROW_NUMBER()函数,生成组内连续且唯一的数字。

--语法:
ROW_NUMBER() OVER(PARTITION BY 字段 (根据字段分组)
ORDER BY 字段 (分组内根据字段排序)
)[alias] FROM emp;
--查看每个部门的工资排名?
select ename,sal,deptno,
row_number() over(partition by deptno order by sal desc) rank
from emp;
--30号部门的MARTIN和WARD的工资一样,而排名却又有先后顺序。

(2)RANK()函数

RANK()函数,生成组内不连续也不唯一的数字,同组内排序字段值一样的记录,生成的数字也一样。
RANK()函数,跳跃排序即:若有相同数据则排名相同 然后跳跃排序 1 2 3 4 4 6

语法:
RANK() OVER(
PARTITION BY 字段1
ORDER BY 字段2)[alias]
FROM emp;
select ename,sal,deptno,rank() over(
partition by deptno
order by sal desc)
from emp;
--30号部门的MARTIN和WARD的工资一样,排名也一样,但下一个同部门的员工JAMES会跳号。

(3)DENSE_RANK()函数

DENSE_RANK()函数,生成组内连续但不唯一的数字,

若有相同数据则排名相同 然后递增排序,如:1 2 3 3 4 5 5 6 7

语法:
DENSE_RANK() OVER(
PARTITION BY
ORDER BY)[alias]
FROM emp;
*/
select ename,sal,deptno,dense_rank() over(
partition by deptno
order by sal desc) rank
from emp;
--30号部门的MARTIN和WARD的工资一样,排名也一样,
--与rank()函数不一样的是,下一个同部门的员工JAMES不会跳号。
posted @   禾喵  阅读(1219)  评论(0编辑  收藏  举报
点击右上角即可分享
微信分享提示