oracle mysql 函数_常用数据库函数汇总(oracle or mysql)

ORACLE:

oracle sql 的空值null的判断和转换：nvl的用法

NVL函数: NVL函数是将NULL值的字段转换成默认字段输出。

NVL(expr1,expr2)

expr1,需要转换的字段名或者表达式。

expr2,null的替代值

oracle sql 的字符串的截取：to_char的用法

//周

to_char(sysdate,'yyyy')

//月

to_char(sysdate,'MM')

//年

to_char(sysdate,'iw')

oracle sql 高级查询之  字符转数字进行排序，同时将null值置为0

nvl(cast(E.num as decimal ),0)

oracle sql 高级查询之  分组并排序

over(partition by ...order by ...)

partition by分区，分组

order by 按。。。

over:在什么条件之上。 partition by e.deptno:按部门编号划分(分区)。 order by e.sal desc:按工资从高到低排序(使用rank()/dense_rank() 时，必须要带order by否则非法) rank()/dense_rank():分级 整个语句的意思就是：在按部门划分的基础上，按工资从高到低对雇员进行分级，“级别”由从小到大的数字表示(最小值一定为1)。

那么rank()和dense_rank()有什么区别呢？ rank():跳跃排序，如果有两个第一级时，接下来就是第三级。 dense_rank():连续排序，如果有两个第一级时，接下来仍然是第二级。

select e.ename, e.job, e.sal, e.deptno

from (select e.ename,

e.job,

e.sal,

e.deptno,

rank() over(partition by e.deptno order by e.sal desc) rank

from scott.emp e) e

where e.rank = 1;

select e.ename, e.job, e.sal, e.deptno

from (select e.ename,

e.job,

e.sal,

e.deptno,

dense_rank() over(partition by e.deptno order by e.sal desc) rank

from scott.emp e) e

where e.rank = 1;

min()和max()，前者求最小值，后者求最大值

select e.ename,

e.job,

e.sal,

e.deptno,

nvl(e.sal - min(e.sal) over(partition by e.deptno), 0) diff_min_sal,

nvl(max(e.sal) over(partition by e.deptno) - e.sal, 0) diff_max_sal

from scott.emp e;

lead(列名,n,m):当前记录后面第n行记录的的值，没有则默认值为m；如果不带参数n,m，则查找当前记录后面第一行的记录的值，没有则默认值为null。 lag(列名,n,m):当前记录前面第n行记录的的值，没有则默认值为m；如果不带参数n,m，则查找当前记录前面第一行的记录的值，没有则默认值为null。

select e.ename,

e.job,

e.sal,

e.deptno,

first_value(e.sal) over(partition by e.deptno) first_sal, --第一个

last_value(e.sal) over(partition by e.deptno) last_sal, --最后一个

sum(e.sal) over(partition by e.deptno) sum_sal, --总和

avg(e.sal) over(partition by e.deptno) avg_sal,--平均值

count(e.sal) over(partition by e.deptno) count_num, --数量

row_number() over(partition by e.deptno order by e.sal) row_num --行号

from scott.emp e;

重要提示：大家在读完本片文章之后可能会有点误解，就是OVER (PARTITION BY ..)比GROUP BY更好，实际并非如此，前者不可能替代后者，而且在执行效率上前者也没有后者高，只是前者提供了更多的功能而已，所以希望大家在使用中要根据需求情况进行选择