Oracle相关函数学习整理

to_char

项目用于将DATE类型的日期转为固定格式的字符串输出

//将sql中的date字段安指定格式的字符串输出
select to_char(sysdate, 'yyyy-MM-dd') from dual;
//逗号,可以作为分割符使用，只能用在整数部分
select to_char(12000, '99,99,9') from dual;
//TO_CHAR(待格式化数据, 'FM99999990.00')
//①其9代表：如果存在数字则显示数字，不存在则显示空格
//②其0代表：如果存在数字则显示数字，不存在则显示0，即占位符。
//③其FM代表：删除如果是因9带来的空格，则删除之
select to_char(0.03212, 'FM999990.0000') from dual;
//还有其它转换，美刀符号啥的，参考：https://zhuanlan.zhihu.com/p/24866384

decode

decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)
oracle特有的

select decode(6, 1, '值1', 2, '值2', '值3') from dual;
//结合sign比较大小
//sign 根据某个值是正数，负数，0分别返回1，-1，0
select decode(sign(10 - 20), -1, 20, 1, 10, 10) from dual;

case when then else end

也是条件判断，作用和decode类似。
区别：
1、decode是oracle独有的，case适用于oracle、mysql、sqlserver
2、decode只能做相等比较，case可以是>；<； = ；is null
参考：https://zhuanlan.zhihu.com/p/29409967

select case when 5 > 3 then '5大于3'
            when 5 < 3 then '5小于3'
            else '5等于3'
            end result from dual;

to_number

• 字符串转数值类型

//结合trunc使用
select trunc(to_number(123.123), 2) num from dual;

• 转换一个十六进制数的浮标

select to_number('0A', 'XX') num from dual;

• 一个十六进制数转换为十进制

select to_number (10,'XXXXXXXX') num from dual;

NVL 和 NVL2

NVL2(expr1,expr2,expr3)
　　功能：如果参数表达式expr1值为NULL，则NVL2()函数返回参数表达式expr3的值；如果参数表达式expr1值不为NULL，则NVL2()函数返回参数表达式expr2的值。

select nvl2('', 3, 5) from dual;
select nvl2(null, 3, 5) from dual;
select nvl2(1, 3, 5) from dual;

NVL( string1, replace_with)
　　功能：如果string1为NULL，则NVL函数返回replace_with的值，否则返回string1的值，如果两个参数都为NULL ，则返回NULL。

select nvl('', 3) from dual;
select nvl(null, 3) from dual;

||

相当于concat函数，连接符的作用

select 'ab' || 'cd' from dual;

round

截取数字，四舍五入

//round(number, decimals) decimals不取的话，不取小数
select round(123.65, 1) num from dual;

abs

取绝对值

select abs(-2) num from dual;

rownumber

看不懂这个字段啥意思。
参考：https://www.cnblogs.com/CandiceW/p/6869167.html

//对于相等的判断rownumber=1可以查到数据
//对于相等判断rownumber=2不可以查到数据,不知道什么原因
select * from student where  rownumber = 1 
select * from student where  rownumber = 2
//对于不相等的判断,大于的条件不可以查到数据，不知道原因
//对于不相等的判断,小于的条件可以获取数据
select * from student where rownumber > 2 //无法获取数据
select * from student where rownumber < 4 //可以获取数据
//大于的条件判断可以通过子查询来获取，但rownumber 必须取别名
select student, name from (select student, name, rownumber no) where no > 3

row_number

1、row_number() over (order by col_1[,col_2 …])
按照col_1[,col_2 …]排序,返回排序后的结果集，并且为每一行返回一个不相同的值。

2、row_number() over (partition by col_n[,col_m …] order by col_1[,col_2 …])
先按照col_n[,col_m …进行分组，再在每个分组中按照col_1[,col_2 …]进行排序(升序)，最后返回排好序后的结果集

trunc

参考：https://blog.csdn.net/qq_29171935/article/details/89478520
1.TRUNC(date,[fmt])
date 为必要参数，是输入的一个date日期值
fmt 参数可忽略，是日期格式，缺省时表示指定日期的0点。
2.TRUNC(number,[decimals])
number 为必要参数，是输入的一个number数值
decimals 参数可忽略，是要截取的位数，缺省时表示截掉小数点后边的值。
与round比，没有四舍五入

//1
select trunc(sysdate, 'yy') from dual;
//2
select trunc(123.65) from dual;

union 和 union all

Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；

Union All：对两个结果集进行并集操作，包括重复行，不进行排序；
没有表数据，未验证
参考：https://www.cnblogs.com/yuanchaoyong/p/6213885.html

group by

分组函数
select子句后的任一非聚合函数字段都应来源于group by 分组语句后，否则语法会编译不通过。

row_number() 、rank()、dense_rank()

排序，常配合order by 、partition by使用
参考：Oracle中row_number()、rank()、dense_rank() 的区别
oracle rank()函数讲解
Oracle Partition By 的使用

substr

substr函数格式 (俗称：字符截取函数)
　　格式1： substr(string string, int a, int b);
　　 1、string:要截取的字符串
　　 2、a 截取字符串的开始位置（注：当a等于0或1时，都是从第一位开始截取）
　　 3、b 要截取的字符串的长度
　　格式2：substr(string string, int a) ;
　　 1、string 需要截取的字符串
　　 2、a 可以理解为从第a个字符开始截取后面所有的字符串

//格式1，下面的两个sql结果都是hell,当a等于0或1时，都是从第一位开始截取
select substr('hello sql',0,4) from dual;

select substr('hello sql',1,4) from dual;
//格式2，下面sql结果‘lo sql’
select substr('hello sql',4) from dual;

add_months

add_months 函数主要是对日期函数进行操作
　　格式1： add_months(date, num);
　　 1、date:要截取的字符串
　　 2、num:需要增加的天数（可为正负）

//查询三个月后的日期
select  add_months(sysdate, 3) from dual;

lag() lead()

Lag和Lead分析函数可以在同一次查询中取出同一字段的前N行的数据(Lag)和后N行的数据(Lead)作为独立的列。
参考：https://blog.csdn.net/pelifymeng2/article/details/70313943
格式：
lag(exp_str, offset, defval) over(partion by order by）

      exp_str:需要取值字段
      offset:偏移字段，默认1
      defval:默认值