oracle 小知识点

oracle 默认排序空值在后面，显示在前面用nvl(column,-1) 或nulls first 与之对应的是nulls last

 

取手机号码的后四位

substr(phoneNum,-4)

 

日期

trunc(date,'yy') 年初

trunc(date,'mm') 月初

 

with 用法

with类似创建一张临时表

with e as (select rownum sn,dept_code,job from v)

select dept_code from e;

 

正则表达式

regexp_replace

regexp_count

select regexp_replace(data,'[^0-9]','') dname from dept;

注意^的位置在方括号内，所有字符之前，是除[0123456789]的意思，如果不在方括号内，则表示字符串开始的位置'^hell'

 

select id,case when trx='PY' then '取款' else '存款' end  存取类型,amt 金额,

sum(case when trx='PY' then -amt else amt end) over(order by id)

from v order by id;

 

select deptno,empno,ename,sal,first_value(ename) over(partition by deptno order by empno) as 工资最高的人min,

first_value(ename) over(partition by deptno order by empno desc) as 工资最高的人max

from emp

where deptno=20 

order by 1,4 desc;

 

 

with t1 as

(select grouping(t.b) as gp_b,

t.b as 品牌,

sum(case t.a when '门店1' then t.c end) as 销量_门店1,

sum(case t.a when '门店2' then t.c end) as 销量_门店2,

sum(case t.a when '门店3' then t.c end) as 销量_门店3,

sum(t.c) as 销量_合计,

sum(case t.a when '门店1' then t.d end) as 收入_门店1,

sum(case t.a when '门店2' then t.d end) as 收入_门店2,

sum(case t.a when '门店3' then t.d end) as 收入_门店3,

sum(t.d) as 收入_合计

from T t

group  by  rollup(t.b)

order by 1 desc ,2)

select case when gp_b=1 then '销量合计' else 品牌 end as 品牌,

销量_门店1 as 门店1,

销量_门店2 as 门店2,

销量_门店3 as 门店3,

销量_合计 as 合计

from t1

union all

select case  when gp_b=1 then '收入合计' else 品牌 end  as 品牌,

收入_门店1 as 门店1,

收入_门店2 as 门店2,

收入_门店3 as 门店3

收入_合计  as 合计

from t1

union all

select case when gb_b=1 then '收入合计' else 品牌 end as 品牌，

round(销量_门店1*收入_门店1/销量_合计,2) as 门店1,

round(销量_门店2*收入_门店2/销量_合计,2) as 门店2,

round(销量_门店3*收入_门店3/销量_合计,2) as 门店3,

收入_合计  as 合计

from t1;

 

 

--01创建序列

create sequence SEQ_SENDHDFS_ROUTE

minvalue 1

maxvalue 99999999999999999999

start with 1

increment by 1

cache 200;

 

--02创建job

declare  waybill_basic_job number;  

begin  

dbms_job.submit(waybill_basic_job,'STP_TEMP_WAYBILL;',sysdate,'TRUNC(sysdate+1) +1/24');  

commit;  

end;

 

begin   

dbms_job.remove(900);  

commit;  

end; 

 

--03创建同义词

create synonym TM_CVYGAP_ROUTE for OMPROUTE.TM_CVYGAP_ROUTE;

commit;