Oracle 函数高级查询

• oracle高级查询
  • Oracle SQL获取每个分组中日期最新的一条数据
  • 求平均值(为0的参数不均摊)
  • 字符串清除前面的0
  • 判断字符串串是否包含某个字符串
  • switch 判断
  • oracle不足位数补零
  • 根据上级查询下级
  • 行转列
  • 列转行
  • 删除表中重复数据，重复数据是根据单个字段（Name）来判断，只留有rowid最小的记录
  • 查询第几次出现，也可以删除重复字
  • 判断是否为数字
  • 数据存在修改，不存在插入

oracle高级查询

Oracle SQL获取每个分组中日期最新的一条数据

select rn,a,b,c
  from (select ROW_NUMBER() OVER(PARTITION BY a ORDER BY b DESC) rn,a,b,c
              from table)

where rn = 1

求平均值(为0的参数不均摊)

select

SUBSTR(PERIOD,1,4) 年份,

SUBSTR(PERIOD,5,2)||'月' 期间,

'ORG_'||code 组织,

'不分人员'人员,

'DEPT1_'|| u9_biz_dept 部门,

round(sum_p/sum_month,6)

from (

select a.PERIOD,mud.code ,md.u9_biz_dept,

count(1) 人数，

SUM (count(1)) OVER (PARTITION BY SUBSTR (PERIOD, 1, 4),md.u9_biz_dept order by PERIOD) sum_p,

row_number() OVER (PARTITION BY SUBSTR (PERIOD, 1, 4)，md.u9_biz_dept order by PERIOD) sum_month

from dw.DW_EHR_EMPLOYEE_BASE a left join V_nc_to_u9_dept md on md.nc_code=a.DEPTCODE

 left join ods.md_u9_dept mud on md.u9_pk_org=mud.pk_dept

where SUBSTR(a.PERIOD,1,4)>='2018' 

group by a.PERIOD，mud.code  ,md.u9_biz_dept

order by PERIOD

)

字符串清除前面的0

    LTRIM('00090','0')

判断字符串串是否包含某个字符串

instrcontains(carnumber, '粤')
CONTAINS( address, '"HEIBEI province"' )
instr(carnumber, '粤') > 0
dd.carnumber like '赣%'

switch 判断

decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)

该函数的含义如下：
IF 条件=值1 THEN
　　　　RETURN(翻译值1)
ELSIF 条件=值2 THEN
　　　　RETURN(翻译值2)
　　　　......
ELSIF 条件=值n THEN
　　　　RETURN(翻译值n)
ELSE
　　　　RETURN(缺省值)
END IF


decode(字段或字段的运算，值1，值2，值3）


       这个函数运行的结果是，当字段或字段的运算的值等于值1时，该函数返回值2，否则返回值3
当然值1，值2，值3也可以是表达式，这个函数使得某些sql语句简单了许多


使用方法：


1、比较大小
select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; --取较小值
sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1
例如：
变量1=10，变量2=20
则sign(变量1-变量2)返回-1，decode解码结果为“变量1”，达到了取较小值的目的。

oracle不足位数补零

select rpad('AAA',5,'0') from dual; 这样就可以了
【注意】
1.'AAA'为待补字符；5表示补齐后的总字符长度；0表示不足时补什么字符
2.rpad是右侧补0，左侧补0可用lpad

根据上级查询下级

select
        *
    from
        fht_enterprise  start with id = 1218 connect
    by
        prior id =  parentid
start with 子句：遍历起始条件，有个小技巧，如果要查父结点，这里可以用子结点的列，反之亦然。
connect by 子句：连接条件。关键词prior，prior跟父节点列parentid放在一起，就是往父结点方向遍历；prior跟子结点列subid放在一起，则往叶子结点方向遍历，     parentid、subid两列谁放在“=”前都无所谓，关键是prior跟谁在一起。
order by 子句：排序，不用多说。

数状图上层级汇总下层级数据
with t as (    
select a.pk_dept,a.dept_type,SYS_CONNECT_BY_PATH(a.pk_dept,'/')||'/' paths from ods.md_u9_dept a
start with pk_dept = '~' connect
by prior pk_dept =  pk_fatherdept)
select c.*,
(select sum(dept_type) from t where  t.paths like c.paths||'%' )  sum_value
from t c

根据时间累加或累减值

select tt.item_no,
       tt.period,
       cmu-nvl(lag(cmu,1)over(partition by tt.item_no,substr(tt.period,1,4) order by tt.period),0) --上一月-这个月值
from tmp_cmu tt
order by 1,2


select a.PERIOD,corg_code,
   count(1) 人数，
   SUM (count(1)) OVER (PARTITION BY SUBSTR (PERIOD, 1, 4) order by PERIOD) sum_month --累计所有月份值,
   count(1) OVER (PARTITION BY SUBSTR (PERIOD, 1, 4) order by PERIOD) sum_month1
from dw.DW_EHR_EMPLOYEE_BASE a
where SUBSTR(a.PERIOD,1,4)>='2018' and corg_code='800'
group by a.PERIOD，corg_code order by PERIOD

行转列

with temp as(
select a.seccode,a.secname,a.F006N, a.F007N, a.F008N,  a.F009N from CI_DATAS_P_STOCK2303 a where  a.enddate='2018-12-31'
)
select seccode,secname,name,title from
temp
unpivot
(name for title in (F006N, F007N, F008N, F009N))t

列转行

SELECT * FROM [StudentScores] /*数据源*/
AS P
PIVOT
(
    SUM(Score/*行转列后 列的值*/) FOR
    p.Subject/*需要行转列的列*/ IN ([语文],[数学],[英语],[生物]/*列的值*/)
) T

例：
SELECT t.* FROM ( select sdata,dim_s,dim_y||dim_m a  from C1_OUTPUT_DATAS_FI01 where stype ='财务报表与指标')
PIVOT (sum(sdata) m1 ,max(a) m2 FOR dim_s IN ('实际' as 实际,'同比增长率' as 同比) )t  

删除表中重复数据，重复数据是根据单个字段（Name）来判断，只留有rowid最小的记录

delete from OA_ADDRESS_BOOK where (Name) in
(select Name from OA_ADDRESS_BOOK group by Name having count(Name) >1)
and rowid not in (select min(rowid) from OA_ADDRESS_BOOK group by Name having count(Name)>1)

查询第几次出现，也可以删除重复字

select * from (
  select  
  ROW_NUMBER() OVER(PARTITION BY  bp.code ORDER BY  hpo.joinsysdate DESC) rn,
  
to_char( to_date(hpo.joinsysdate,'yyyy-MM-dd  HH24:mi:ss'),'yyyy') 合并,
  'PEO'||to_char(to_number(bp.code)) 元素,
  bp.name||'_'||to_char(to_number(bp.code)) 中文名,
  hpo.endflag 失效
from ods.bd_psndoc bp
left join ods.hi_psnorg hpo on bp.pk_psndoc =  hpo.pk_psndoc
left join ods.hi_psnjob hpj on hpo.pk_psnorg =  hpj.pk_psnorg
  and hpj.lastflag = 'Y' and hpj.ismainjob = 'Y'
WHERE hpo.indocflag = 'Y'
   AND hpo.psntype = 0
   AND bp.code != '15421'   
order by hpo.joinsysdate desc  
) where  rn = 1

判断是否为数字

查询是否为数字：select * from table where regexp_like(column,'^[0-9]+[0-9]$');
查询非数值类型：regexp_replace(字段,'^[-\+]?\d+(\.\d+)?$','')
正则查询以字段开头：regexp_like(NATACC_CODE4,  '^(6001|6061|6401|1801|2201|2241|2203|2202)')

create or replace function Isnumber(P_column in varchar2) return number
is
l_t number;
begin
  l_t := to_number(P_column) ;
     return 1;
  exception when others then
     return 0;
end;

数据存在修改，不存在插入

merge法
--适合与两张表的关联查询
MERGE INTO table_name alias1   
USING (table|view|sub_query) alias2  
ON (join condition)   
WHEN MATCHED THEN   
    UPDATE table_name SET col1 = col_val1
WHEN NOT MATCHED THEN   
    INSERT (column_list) VALUES (column_values);
--例：
merge into Account t1  
using (select '3' AccountID,'肖文博' AccountName from dual) t2  
on (t1.AccountID = t2.AccountID)  
when matched then  
     update set t1.AccountName = t2.AccountName
when not matched then  
     insert values (t2.AccountID, t2.AccountName);  
commit;

--
no_data_found法
先查找要插入的记录是否存在，存在则修改，不存在则插入。用于防止插入冗余数据。
declare t_cols number;
begin
select AccountName into t_cols from account where AccountID = '8';
exception
when no_data_found then begin
   --dbms_output.put_line('添加');
   insert into account(AccountID,AccountName) values('8','添加-8');
end;
when others then
  begin
    --dbms_output.put_line('修改');
    update account set AccountName = '修改-8' where AccountID = '8';
end;
end;

表的存在/删除/创建
declare num number;   
begin
    select count(1) into num from user_tables where table_name='ACCOUNT';   
    if num > 0 then   
      dbms_output.put_line('存在!');
      execute immediate 'drop table ACCOUNT ';
    end if;   
      execute immediate 'create table Account
                        (
                                AccountID nvarchar2(50) primary key,
                                AccountName nvarchar2(50)
                        )';  
      dbms_output.put_line('成功创建表!');
end;

隐式游标法：SQL%NOTFOUND/SQL%FOUND
SQL%NOTFOUND 是SQL中的一个隐式游标，在增删查改的时候自动打开，如果有至少有一条记录受影响，都会返回false
先根据唯一ID到数据表中修改一条记录，如果这条记录在表中存在，则修改，并且SQL%NOTFOUND返回false。如果修改的记录不存在，SQL%NOTFOUND返回true，并且执行插入语句
begin
update account set AccountName = '修改-a' where AccountID = '5';
IF SQL%NOTFOUND THEN
   insert into account(AccountID,AccountName) values('5','添加-b');
END IF;
end;

异常法：DUP_VAL_ON_INDEX
当往表中插入一条数据，因为表中有主键约束，如果插入的数据在表中已经存在，则会抛出异常，在异常抛出后进行修改。用于插入指定id的数据。
begin
insert into account(AccountID,AccountName) values('6','添加-b');
exception
when DUP_VAL_ON_INDEX then begin
update account set AccountName = '修改-b' where AccountID = '6';
end;
end;


虚拟表法：dual
dual是一个虚拟表，用来构成select的语法规则，oracle保证dual里面永远只有一条记录。
先声明一个变量t_count,表dual表的值赋给t_count，如果这个值小于1，表示记录不存在，进行插入操作，反之，存在就进行修改操作。用于防止数据冗余。
declare t_count number;
begin
select count(*) into t_count from dual where exists(select 1 from account where AccountID='11');
if t_count< 1 then
  dbms_output.put_line('添加');
  insert into account(AccountID,AccountName) values('11','添加-11');
else
  dbms_output.put_line('修改');
  update account set AccountName = '修改-11' where AccountID = '11';
  end if;
end;