基础函数
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---对某个字段累加求和
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)----将多行合并成一行