因为conference表的joinersid字段是多个工号组成的字符串,所以用以下语句建个字段拆分的视图
CREATE OR REPLACE VIEW too_hyqd2app AS SELECT c.id hydm, regexp_substr(c.joinersid,'[^,]+',1,LEVEL,'i') AS gh FROM conference c
CONNECT BY level<=LENGTH(c.joinersid)-LENGTH(regexp_replace(c.joinersid,',',''))+1
查询结果如下加上简单的where 查询就很久都不出来请问怎么解决
plsql中直接查 select * from too_hyqd2app;只是返回了前几条。加上where的话则需要对视图中全部数据进行查询。数据量过大的话,差距会很明显。
你写的sql是这个意思吧:
with tab as (
select 1 id, 'a-11,b-22,c-33,d-44' str from dual union all
select 2 id, 'a-11,b-22,c-33,d-44' str from dual union all
select 3 id, 'a-11,b-22,c-33,d-44' str from dual
)
select regexp_substr(t1.str, '[^,]+', 1, level) from tab t1
connect by level <= 4;
它本应该返回12条,但是实际返回了3 + 3 * 3 + 3 * 3 * 3 + 3 * 3 * 3 * 3 = 120条。你这个视图少说也有几千万条吧。加上where就需要在没有索引的几千万条记录中搜索,肯定慢。
正确的写法是:
with tab as (
select 1 id, 'a-11,b-22,c-33,d-44' str from dual union all
select 2 id, 'a-11,b-22,c-33,d-44' str from dual union all
select 3 id, 'a-11,b-22,c-33,d-44' str from dual
)select regexp_substr(t1.str, '[^,]+', 1, level) from tab t1
connect by level <= 4
and prior t1.id = t1.id
and prior sys_guid() is not null
;
其实我是想把conference表里的joinersid给拆分出来的,这里存的是不确定数量的工号,想把它完全结构化,请问您说的这个方法可以做到吗
其实我是想把conference表里的joinersid给拆分出来的,这里存的是不确定数量的工号,想把它完全结构化,请问您说的这个方法可以做到吗可以的,这个语句就是干这个的with tab as (
select 1 id, 'a-11,b-22,c-33,d-44' str from dual union all
select 2 id, 'a-11,b-22,c-33,d-44' str from dual union all
select 3 id, 'a-11,b-22,c-33,d-44' str from dual
)select id, regexp_substr(t1.str, '[^,]+', 1, level)
from tab t1
connect by level <= regexp_count(t1.str, ',')
and prior t1.id = t1.id
and prior sys_guid() is not null;
instr(t.ca, ';', 1, c.lv + 1) - (instr(t.ca, ';', 1, c.lv) + 1)) AS c
from
(select a,b,';' || c || ';' AS ca,length(c || ';')
- nvl(length(REPLACE(c, ';')), 0) AS cnt FROM TEMP07031)t--比如字段分别是啊,a,b,c;
可以把根据从字段;分出多行,在求和