select r1.zimu, (case when r2.total is null then 0 else r2.total end) total from (select chr(num + 64) zimu from (select rownum as num from dba_tables where rownum <= 26)) r1 left join (select t2.zimu, count(t1.字段名) total from 表名 t1, (select chr(num + 64) zimu from (select rownum as num from dba_tables where rownum <= 26)) t2 where 字段名 like '%' || t2.zimu || '%' group by t2.zimu) r2 on r1.zimu = r2.zimu order by r1.zimu
try this:select zimu, sum(decode(instr(字段1, zimu), 0, 0, 1)) as nums from (select tbl.zimu, 表名.字段1 from (select chr(num + 64) zimu from (select rownum as num from dba_tables where rownum <= 26)) tbl, 表名) group by zimu order by zimu;
如果是 ab, bc, bf 以上的方法就不行了 select r2.str, count(1) from ( select regexp_substr(r1.str,'[[:alnum:]]*',1,LEVEL*2-1) str from r1 connect by rownum <= length(r1.str) - length(replace(r1.str, ',', '')) ) r2 group by r2.str;
貌似都没有考虑A,A,A,A,A,A,A这种情况啊
declare type arr is varray(100) of varchar2(100); a arr; b arr:=arr(null); v_str salgrade.col%type; k number := 1; v_pos number; n number; begin select col bulk collect into a from salgrade where col is not null; for i in 1..a.count loop v_str := a(i);
v_pos := instr(v_str,',',1,1);
if v_pos = 0 then if b.count < k then b.extend(1); end if; b(k) := v_str; k := k+1; else while v_pos >0 loop v_pos := instr(v_str,',',1,1);
if v_pos > 0 then if b.count < k then b.extend(1); end if; b(k) := substr(v_str,1,v_pos-1); else if b.count < k then b.extend(1); end if; b(k) := v_str; end if;
v_str := substr(v_str,v_pos+1); k := k+1; end loop; end if; end loop;
for a in 1..b.count loop n := 0; for c in 1..b.count loop if b(c) = b(a) then n := n+1; end if; end loop; dbms_output.put_line('b('||a||')='||b(a)||' number is '||n); end loop; end;
select substr('a,b,c',rownum,1) from dual connect by rownum<=1length('a,b,c')把这个写成函数,然后在调用!
with t1 as( select 'A,C,D' a from dual union all select 'A,B,C,F' from dual union all select 'C,F' from dual),t2 as( select wm_concat(a)a from t1)
select a, count(1) counts from (select substr(b.a, instr(',' || b.a, ',', 1, a.rn), instr(b.a || ',', ',', 1, a.rn) - instr(',' || b.a, ',', 1, a.rn)) a from (select rownum rn from dual connect by rownum <= (select length(a) - length(replace(a, ',')) + 1 from t2)) a, t2 b) group by a order by a;
不知道楼主的问题是抽象出来的,还是具体的问题。如果是具体问题,上面的朋友的答案或许已符合你的要求。我写了一个过程比上面的答案稍微通用一些,请楼主择优选择:create or replace procedure pp authid current_user is str varchar2(1000) := 'select null ID from dual '; tstr varchar2(100); i number := 1; begin for cur1 in (select * from t) loop i := 1; while i <= length(cur1.txt) loop
if instr(cur1.txt, ',', i, 1) != 0 then tstr := substr(cur1.txt, i, instr(cur1.txt, ',', i, 1) - i); i := instr(cur1.txt, ',', i, 1) + 1;
else tstr := substr(cur1.txt, i); str := str || 'union all select ''' || tstr || ''' from dual '; exit; end if; str := str || 'union all select ''' || tstr || ''' from dual '; end loop; end loop; /*dbms_output.put_line(substr(str, 1, 200)); dbms_output.put_line(substr(str, 201, 400)); dbms_output.put_line(substr(str, 401, 600));*/ execute immediate 'create or replace view myvw as ' || str; execute immediate 'create or replace view ID_Count as select ID ,count(*) count from myvw group by ID order by ID'; end pp;过程将会生成一个ID_Count的视图,就是楼想要的。
SELECT LENGTH(TRANSLATE('JLDW,JLDSJFLSDK,,,,JLFDSJFLQ3I,(8&*',','||'JLDW,JLDSJFLSDK,,,,JLFDSJFLQ3I,(8&*','*')) FROM DUAL;
select r1.zimu,
(case
when r2.total is null then
0
else
r2.total
end) total
from (select chr(num + 64) zimu
from (select rownum as num from dba_tables where rownum <= 26)) r1
left join (select t2.zimu, count(t1.字段名) total
from 表名 t1,
(select chr(num + 64) zimu
from (select rownum as num
from dba_tables
where rownum <= 26)) t2
where 字段名 like '%' || t2.zimu || '%'
group by t2.zimu) r2 on r1.zimu = r2.zimu
order by r1.zimu
from (select tbl.zimu, 表名.字段1
from (select chr(num + 64) zimu
from (select rownum as num
from dba_tables
where rownum <= 26)) tbl,
表名)
group by zimu order by zimu;
select r2.str, count(1) from
(
select regexp_substr(r1.str,'[[:alnum:]]*',1,LEVEL*2-1) str
from r1 connect by rownum <= length(r1.str) - length(replace(r1.str, ',', ''))
) r2
group by r2.str;
declare
type arr is varray(100) of varchar2(100);
a arr;
b arr:=arr(null);
v_str salgrade.col%type;
k number := 1;
v_pos number;
n number;
begin
select col bulk collect into a from salgrade where col is not null;
for i in 1..a.count loop
v_str := a(i);
v_pos := instr(v_str,',',1,1);
if v_pos = 0 then
if b.count < k then
b.extend(1);
end if;
b(k) := v_str;
k := k+1;
else
while v_pos >0 loop
v_pos := instr(v_str,',',1,1);
if v_pos > 0 then
if b.count < k then
b.extend(1);
end if;
b(k) := substr(v_str,1,v_pos-1);
else
if b.count < k then
b.extend(1);
end if;
b(k) := v_str;
end if;
v_str := substr(v_str,v_pos+1);
k := k+1;
end loop;
end if;
end loop;
for a in 1..b.count loop
n := 0;
for c in 1..b.count loop
if b(c) = b(a) then
n := n+1;
end if;
end loop;
dbms_output.put_line('b('||a||')='||b(a)||' number is '||n);
end loop;
end;
select 'A,C,D' a from dual
union all select 'A,B,C,F' from dual
union all select 'C,F' from dual),t2 as(
select wm_concat(a)a from t1)
select a, count(1) counts
from (select substr(b.a,
instr(',' || b.a, ',', 1, a.rn),
instr(b.a || ',', ',', 1, a.rn) -
instr(',' || b.a, ',', 1, a.rn)) a
from (select rownum rn
from dual
connect by rownum <=
(select length(a) - length(replace(a, ',')) + 1
from t2)) a,
t2 b)
group by a
order by a;
str varchar2(1000) := 'select null ID from dual ';
tstr varchar2(100);
i number := 1;
begin
for cur1 in (select * from t) loop
i := 1;
while i <= length(cur1.txt) loop
if instr(cur1.txt, ',', i, 1) != 0 then
tstr := substr(cur1.txt, i, instr(cur1.txt, ',', i, 1) - i);
i := instr(cur1.txt, ',', i, 1) + 1;
else
tstr := substr(cur1.txt, i);
str := str || 'union all select ''' || tstr || ''' from dual ';
exit;
end if;
str := str || 'union all select ''' || tstr || ''' from dual ';
end loop;
end loop;
/*dbms_output.put_line(substr(str, 1, 200));
dbms_output.put_line(substr(str, 201, 400));
dbms_output.put_line(substr(str, 401, 600));*/
execute immediate 'create or replace view myvw as ' || str;
execute immediate 'create or replace view ID_Count as select ID ,count(*) count from myvw group by ID order by ID';
end pp;过程将会生成一个ID_Count的视图,就是楼想要的。