Orcal常用查询实例集合

本文的初衷主要是记录工作中碰到的一些查询实例，便于后续的工作参考从而提高效率。

一、A表拼接B表的数据，A、B两个表字段相同，当B表有数据时用B表的，否则用A表的。区分粒度为业务日期。

select z.fundid,
       z.busidate,
       z.cloumn1,
       z.cloumn2
  from tablea z
 where z.fundid in (fundids)
   and z.busidate between begindate and enddate
   and z.fundid || z.busidate not in
       (select t.fundid || t.busidate as unioncode
          from tableab t
         group by t.fundid || t.busidate)
union
select t.fundid,
       t.busidate,
       '89' || t.cloumn1,
       t.cloumn2
  from tableab t
 where t.fundid in(fundids)
   and t.busidate between begindate and enddate

 

 二、orcal将多条查询记录拼接成一条记录

select listagg(字段名,',') within GROUP(ORDER BY 字段名)；

例如，查询一个表有3条记录，字段A的值分别是A、B、C。那么使用 listagg 后将返回一条记录A,B,C

select listagg(fundid || fundname || '(' || alternativelevel || ') ') within GROUP(ORDER BY fundid,fundname,alternativelevel) from zt_alternativefundinfo where fundbankid = z.fundbankid and sysfundid = k.fundid and alternativelevel not in ('B','C')

 

三、orcal查询分割字符串

这个跟上面的相反，当一个字段存储是json格式或是以逗号分开的多个id，对应关联表的多条数据。我们希望用in 来查询，返回多条记录，因此需要分割这个字段的值。

regexp_substr(string, pattern, position, occurrence, modifier)

__srcstr     ：需要进行正则处理的字符串
__pattern    ：进行匹配的正则表达式
__position   ：起始位置，从第几个字符开始正则表达式匹配（默认为1）
__occurrence ：标识第几个匹配组，默认为1
__modifier   ：模式（'i'不区分大小写进行检索；'c'区分大小写进行检索。默认为'c'。）

例如下面的将 '123'拆分成 1、2、3共三条数据

select regexp_substr ('1,2,3', '[^,]+', 1,rownum) from dual connect by rownum<=length ('1,2,3') - length (regexp_replace('1,2,3', ',', ''))+1;

 四、not in优化

首先外表大内表小用in，外表小内表大则用exists

1、对于not exists查询，内表存在空值对查询结果没有影响；对于not in查询，内表存在空值将导致最终的查询结果为空。
2、对于not exists查询，外表存在空值，存在空值的那条记录最终会输出；对于not in查询，外表存在空值，存在空值的那条记录最终将被过滤，其他数据不受影响。

使用 not in 查询一个结果980万条的SQL用时3.918s，使用链表查询则用时3.622s多，测试了很多次链表确实比not in快一点

-- not in 3.918s
select *
  FROM tablea
 WHERE .. .
    AND id not in (select id from tableb);
-- left 3.622s
select a.*
  FROM tablea a
  left join tableb
    on a.id = b.id
 WHERE .. .
    AND a.id is null;