常用数据库函数汇总(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更好,实际并非如此,前者不可能替代后者,而且在执行效率上前者也没有后者高,只是前者提供了更多的功能而已,所以希望大家在使用中要根据需求情况进行选择


  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值