oracle常用函数

基础函数

select * from table order by id nulls first/last ---将id是空值的放在首位还是末尾

select nvl2(val1,val2,val3) from dual----如果val1是null，则返回nvl3,否则返回nvl2

select coalesce(null,null,.....value) from dual---判断值是否为空，直到返回一个不为null的值

replace('i love you','o','O')---->'I lOve yOu'------->替换函数，

select least(1.2,3,4,5,6,7,8,9) from dual找出最小值

select greatest(1.2,3,4,5,6,7,8,9) from dual找出最大值

select mod(10,4) from dual 取余数

select dbms_random.value(1.10) from dual1--10的随机数小数

select distinct(trunc(dbms_random.value(1.10))) from dual connect by level<=1--10的随机数

select nullif('2','1’) from dual ----样返回null,不一样返回2

select * from (select  * from m_cons_base_load_cnf ) t unpivot (for load in (fh1 AS 'fh1',fh2 AS 'fh2',fh3AS 'fh3'))----列转行

select * from (select  cons_no,cur_load,to_char(date_time,'hh24:mi')datatime from m_cons_peak_base_curve ) t pivot (sum(cur_load) for datatime in ('00:00' AS 'm1','00:15' AS 'm2','00:30' AS 'm3'))----行转列

 

分区函数

select*from vw_org d start with org_no=33401 connect by d.org_no=prior d.p_org_no--显示父级（递归函数）

select *SUM(money) over(order by(money)DESC) from dual---对某个字段累加求和

select *avg(money) over(order by(money)DESC) from dual---对某个字段累加平均

select sal,ename,deptno,
sum(sal)over(partition by deptno order by ename) 累计总工资,
avg(sal)over(partition by deptno order by ename) 累计平均工资
from emp;---根据部门累加总工资

select ename,sal,
rank()over(ORDER BY sal desc) 按员工的姓名进行排名，工资一样的并列排名 
from emp;

select ename,sal,deptno,
rank()over(PARTITION BY deptno ORDER BY sal desc) 按部门进行排名，工资一样的并列排名 
from emp;

select *
from (select e.*,
rank()over(PARTITION BY deptno ORDER BY sal desc) 查询每个部门排名第一
from emp e)
where 排名=1;

select name,money,money-lead(money)over(order by money) from test002---根据金额推出下一个,按照工资降序排序查询每一个人工资比他下一个人多多少：（lag()是找上一个）

row_number)over(partition by yh.cons_no,yh.tmnl_bar_code order by yh.s_location)n,--去重两条数据中的分路，where n=1

row_number)over(partition by dw.org_no order by sum(air_down_load) dese nulls last )n,--取负荷当中最大的负荷，where n=1

max(air_down_load),max(data_time)keep (dense_rank first order by air_down_load  desc nulls last)---取最大负荷，最大负荷的时间

count(yh.cons_no) over(partition by yh.cons_no order by yh.s_location)(分区函数)
select org_name,count(*)over() from vw_org where org_type in (20)--对每一行都计数

select listagg(org_no,',') within group (order by org_no ) from vw_org where org_type in (20)----将多行合并成一行