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不会跳号。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步