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

一、DECODE()函数

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

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

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

参数说明：

①该函数用于比较expr的值，如果匹配到哪个search条件，就返回对应的result结果。
②可以有多组search和result的对应关系，如果都没匹配到，则返回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 @ 2022-06-09 01:26 禾喵阅读(1483) 评论(0) 收藏举报

Coder Cat