oracle中行转列并排序与oracle查询优化

1，oracle中行转列并排序

with a as (select 'ABC,AA,AD' id,'X' AS Name from dual
union ALL select 'ABC,AA,AD,ABD,JI,CC,ALSKD,ALDKDJ' id,'Y' AS NAme from dual)
select a.NAME,case 
when length(regexp_replace(id,'[^,]+')) IS null then a.id 
when b.NUM = 1   then SUBSTR(a.ID,0,instr(a.ID , ',' ,'1','1')-1) 
when b.NUM = length(regexp_replace(id,'[^,]+')) + 1  then 
SUBSTR(a.ID,instr(a.ID , ',' ,'1',b.NUM-1)+1)
else
SUBSTR(a.ID, instr(a.ID , ',' ,1,b.NUM-1)+1, instr(a.ID , ',' ,1,b.NUM) - instr(a.ID , ',' ,1,b.NUM - 1) - 1)
end as x
from a
join (select  1+(rownum - 1) num  from  dual connect by rownum <= 1000) b on b.NUM <= nvl(length(regexp_replace(id,'[^,]+')),0) + 1 order by a.NAME,b.num
sql解释：

with a as (select 'ABC,AA,AD' id,'X' AS Name from dual
union ALL select 'ABC,AA,AD,ABD,JI,CC,ALSKD,ALDKDJ' id,'Y' AS NAme from dual)

定义一个a的表：name是X，Y，对应的列id是用逗号拼接起来的数据

要转化为，id，name一一对应

select a.NAME,case 
when length(regexp_replace(id,'[^,]+')) IS null then a.id 
when b.NUM = 1   then SUBSTR(a.ID,0,instr(a.ID , ',' ,'1','1')-1) 
when b.NUM = length(regexp_replace(id,'[^,]+')) + 1  then 
SUBSTR(a.ID,instr(a.ID , ',' ,'1',b.NUM-1)+1)
else
SUBSTR(a.ID, instr(a.ID , ',' ,1,b.NUM-1)+1, instr(a.ID , ',' ,1,b.NUM) - instr(a.ID , ',' ,1,b.NUM - 1) - 1)
end as x
from a
join (select  1+(rownum - 1) num  from  dual connect by rownum <= 1000) b on b.NUM <= nvl(length(regexp_replace(id,'[^,]+')),0) + 1 order by a.NAME,b.num

这段sql是查询a表，根据逗号分割的数量来分割数据，查询成name，id一一对应的格式。

2，oracle查询优化

1）查询表连接速度：表连接>exist>not exist>in>not in;
因此如果简单提高效率可以用exist代替in进行操作，当然换成表连接可以更快地提高效率，具体是用left join代替not in 和
not exist，用inner ioin 代替in和exist，这样可以大大提高效率。

就是将in的表关联，然后根据关联的表的id是否为null去实现in和not in等

2）列传行查询：listagg>WM_CONCAT

listagg可以排序，而且查询效率高，WM_CONCAT有的版本已经不支持了

listagg查询实例：select listagg(TO_CHAR(USERID)) within group(order by ID),name from forp_text group by name

forp_text:表名；

id：主键id

USERID：人员id

name：分组字段

3）添加索引等