Oracle数据库常用函数

decode(列| 值,判断值，返回值)

SELECT E.ENAME,DECODE(E.JOB,
       		 		 'CLERK','业务员',
          		     'SALESMAN','销售员',
       				 'MANAGER','经理',
       				 'ANALYST','分析员') 职位
 FROM EMP E;

case when 多值判断

第一种：
SELECT E.ENAME,
        CASE E.JOB
        WHEN 'CLERK' THEN '业务员'
        WHEN 'SALESMAN' THEN '销售员'
        WHEN 'MANAGER' THEN '经理'
        WHEN 'ANALYST' THEN '分析员'
        END 职位
FROM EMP E

第二种：

SELECT E.ENAME,E.SAL,CASE
           WHEN E.SAL>3000 THEN '工资很高'
           WHEN E.SAL>2000 THEN '工资一般'
           ELSE '工资一般'
           END 工资等级
FROM EMP E

分析函数

每个部门工资高于部门平均工资的员工人数占整个部门人数的百分比
SELECT A.DEPTNO,
     CONCAT((ROUND(SUM(CASE 
               WHEN A.SAL>A.AVG THEN 1 
               ELSE 0 END)/COUNT(1),2))*100 ,'%') 百分比
FROM (SELECT E.DEPTNO,E.SAL,AVG(SAL) OVER(PARTITION BY E.DEPTNO) AVG
    FROM EMP E)A
GROUP BY A.DEPTNO

排序

RANK(),DENSE_RANK(),ROW_NUMBER()
SELECT E.ENAME,
	   E.DEPTNO
	   E.SAL,
	   RANK() OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL DESC) RANK,
	   DENSE_RANK() OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL DESC) DENSE_RANK,
	   ROW_NUMBER() OVER(PARTITION BY  E.DEPTNO ORDER BY E.SAL DESC) ROW_NUMBER
FROM EMP E;

位移函数
LAG()
LEAD()

SELECT E.ENAME,     
       E.SAL,
       LAG(E.SAL,1)OVER(ORDER BY E.SAL) 按工资排序向下位移,
       LEAD(E.SAL,1)OVER(ORDER BY E.SAL) 按工资排序向上位移
FROM EMP E;

行转换列
pivot()

create table test1
  (deptno number  ,
   cnt    number  
 );
 
insert into test1(deptno,cnt) values (10,3);
insert into test1(deptno,cnt) values (20,5);
insert into test1(deptno,cnt) values (30,6);

select * 
from test1 pivot(sum(cnt) for deptno in (10 deptno10,
                                                              20 deptno20,
                                                              30 deptno30 ))

列转换行
unpivot()

create table sc123
  (name    varchar(10)  ,
   yuwen   number  ,
   shuxue   number,
   yingyu   number   
 );
 insert into sc123(name,yuwen,shuxue,yingyu) values ('Tom',70,85,90);
 insert into sc123(name,yuwen,shuxue,yingyu) values ('Marry',85,81,88);
 
 select * from sc123
 
 列转换行
select * from sc123 
unpivot(score for cno in (yingyu,shuxue,yuwen))

1、instr()函数的格式 （俗称：字符查找函数）
格式一：instr( string1, string2 ) – instr(源字符串, 目标字符串)

格式二：instr( string1, string2 [, start_position [, nth_appearance ] ] ) – instr(源字符串, 目标字符串, 起始位置, 匹配序号)

解析：string2 的值要在string1中查找，是从start_position给出的数值（即：位置）开始在string1检索，
检索第nth_appearance（几）次出现string2。

格式一实例：
select instr('helloworld','l') from dual; 
--返回结果：3    默认第一次出现“l”的位置
select instr('helloworld','lo') from dual; 
--返回结果：4    即“lo”同时出现，第一个字母“l”出现的位置
select instr('helloworld','wo') from dual; 
--返回结果：6    即“wo”同时出现，第一个字母“w”出现的位置

格式二实例：
select instr('helloworld','l',2,2) from dual;  
--返回结果：4    也就是说：在"helloworld"的第2(e)号位置开始，查找第二次出现的“l”的位置
select instr('helloworld','l',3,2) from dual; 
 --返回结果：4    也就是说：在"helloworld"的第3(l)号位置开始，查找第二次出现的“l”的位置
select instr('helloworld','l',4,2) from dual; 
 --返回结果：9    也就是说：在"helloworld"的第4(l)号位置开始，查找第二次出现的“l”的位置
select instr('helloworld','l',-1,1) from dual; 
--返回结果：9    也就是说：在"helloworld"的倒数第1(d)号位置开始，往回查找第一次出现的“l”的位置
select instr('helloworld','l',-2,2) from dual;  
--返回结果：4    也就是说：在"helloworld"的倒数第2(l)号位置开始，往回查找第二次出现的“l”的位置
select instr('helloworld','l',2,3) from dual;  
--返回结果：9    也就是说：在"helloworld"的第2(e)号位置开始，查找第三次出现的“l”的位置
select instr('helloworld','l',-2,3) from dual; 
--返回结果：3    也就是说：在"helloworld"的倒数第2(l)号位置开始，往回查找第三次出现的“l”的位置

格式1： substr(string string, int a, int b);
格式2：substr(string string, int a) ;

解析：
格式1：
1、string 需要截取的字符串
2、a 截取字符串的开始位置（注：当a等于0或1时，都是从第一位开始截取）
3、b 要截取的字符串的长度
格式2：
1、string 需要截取的字符串
2、a 可以理解为从第a个字符开始截取后面所有的字符串。

select substr('HelloWorld',0,3) value from dual; 
--返回结果：Hel，截取从“H”开始3个字符
select substr('HelloWorld',1,3) value from dual; 
--返回结果：Hel，截取从“H”开始3个字符
select substr('HelloWorld',2,3) value from dual; 
--返回结果：ell，截取从“e”开始3个字符
select substr('HelloWorld',0,100) value from dual;
--返回结果：HelloWorld，100虽然超出预处理的字符串最长度，但不会影响返回结果，系统按预处理字符串最大数量返回。
select substr('HelloWorld',5,3) value from dual;
--返回结果：oWo
select substr('Hello World',5,3) value from dual;
--返回结果：o W (中间的空格也算一个字符串，结果是：o空格W)
select substr('HelloWorld',-1,3) value from dual; 
--返回结果：d （从后面倒数第一位开始往后取1个字符，而不是3个。原因：下面红色 第三个注解）
select substr('HelloWorld',-2,3) value from dual; 
--返回结果：ld （从后面倒数第二位开始往后取2个字符，而不是3个。原因：下面红色 第三个注解）
select substr('HelloWorld',-3,3) value from dual;
--返回结果：rld （从后面倒数第三位开始往后取3个字符）
select substr('HelloWorld',-4,3) value from dual;
--返回结果：orl （从后面倒数第四位开始往后取3个字符）

select substr('HelloWorld',0) value from dual; 
--返回结果：HelloWorld，截取所有字符
select substr('HelloWorld',1) value from dual; 
--返回结果：HelloWorld，截取所有字符
select substr('HelloWorld',2) value from dual;  
--返回结果：elloWorld，截取从“e”开始之后所有字符
select substr('HelloWorld',3) value from dual;  
--返回结果：lloWorld，截取从“l”开始之后所有字符
select substr('HelloWorld',-1) value from dual;  
--返回结果：d，从最后一个“d”开始 往回截取1个字符
select substr('HelloWorld',-2) value from dual; 
 --返回结果：ld，从最后一个“d”开始 往回截取2个字符
select substr('HelloWorld',-3) value from dual; 
 --返回结果：rld，从最后一个“d”开始 往回截取3个字符

add_months(x,y)
指定日期加上或减去若干个月。

SELECT ADD_MONTHS(SYSDATE,1) FROM DUAL;
--当前时间增加一个月

last_day()
返回指定日期当月的最后一天

SELECT LAST_DAY(SYSDATE)FROM DUAL;
--返回当月的最后一天

next_day(char,1-7)
返回指定日期下一个星期几所在的日期

SELECT NEXT_DAY(SYSDATE,1)FROM DUAL;
--返回下一个星期一所在的日期

months_between(x,y)
返回两个时间之间相差的月数

trunc(x,y)截取函数

trunc(3.1415926,3)
--返回3.141
trunc(sysdate,'DD')
--返回当月的当天
trunc(sysdate,'DDD')
--返回当月的当天（默认格式）
trunc(sysdate,'D')
--返回当周的第一天
trunc(sysdate,'MM')
--返回当月的第一天
trunc(sysdate,'Q')
--返回当季度的第一天
trunc(sysdate,'Y')
--返回当年的第一天