有张表
class name
1 QQ,ZZ,EE
1 QQ,ZZ,FF
1 QQ,RR,HH
1 EE,EE,EE
1
查询出 name 里 以逗号分割的字符如QQ 出现次数最多的数据
倒叙显示如查询结果
1 EE
1 QQ
1 ZZ
1 RR
1 HH
1 FF不是显示出 只显示出逗号分割后出现最多的字符
1 EE,EE,EE
class name
1 QQ,ZZ,EE
1 QQ,ZZ,FF
1 QQ,RR,HH
1 EE,EE,EE
1
查询出 name 里 以逗号分割的字符如QQ 出现次数最多的数据
倒叙显示如查询结果
1 EE
1 QQ
1 ZZ
1 RR
1 HH
1 FF不是显示出 只显示出逗号分割后出现最多的字符
1 EE,EE,EE
create table table11(c1 int,c2 varchar2(100));
insert into table11
select 1,'QQ,ZZ,EE' from dual union all
select 1,'QQ,ZZ,FF' from dual union all
select 1,'QQ,RR,HH' from dual union all
select 1,'EE,EE,EE' from dual;
--自定义函数返回类型
create or replace type t_ret_table is table of varchar2(20);
--切分函数
create or replace function f_split_string(var_str in string,var_split In String) return t_ret_table
is
var_out t_ret_table;
var_tmp varchar2(4000);
var_element varchar2(4000);
begin
var_tmp := var_str;
var_out := t_ret_table();
--如果存在匹配的分割符
while instr(var_tmp,var_split)>0 loop
var_element := substr(var_tmp,1,instr(var_tmp,var_split)-1);
var_tmp := substr(var_tmp,instr(var_tmp,var_split)+length(var_split),length(var_tmp));
var_out.extend(1);
var_out(var_out.count) := var_element;
end loop;
var_out.extend(1);
var_out(var_out.count) := var_tmp;
return var_out;
end f_split_string;
--存储过程
create or replace procedure sp_test(rst out sys_refcursor) is
tempStr varchar2(4000);
tempID int;
begin
select c1,replace(substr(max(sys_connect_by_path(c2, '|')), 2),'|',',') into tempID,tempStr
from (select c2, c1, row_number() over(partition by c1 order by 1) rn
from table11)
start with rn = 1
connect by rn - 1 = prior rn and c1 = prior c1
group by c1;
--
open rst for
select tempID c1,t.* from table(f_split_string(tempStr,',')) t group by column_value order by count(*) desc
end;
(
select class,substr(name,1,2) name from 表
union all
select class,substr(name,4,2) name from 表
union all
select class,substr(name,4,2) name from 表
)
group by class,name
order by c desc