drop table test1; drop table test; create table test(姓名 varchar2(20));insert into test values('a'); insert into test values('b'); insert into test values('d'); insert into test values('c'); create table test1(id number(2),manageuser varchar(40)); insert into test1 values(1,'a,b,c'); insert into test1 values(2,'a,b'); insert into test1 values(3,'a,b,c,d'); insert into test1 values(4,'a,c'); select tb1.姓名,count(*) from test tb1, (select regexp_substr(manageuser, '[^,]+', 1, level) 姓名 from test1 tb2 connect by level <= regexp_count(manageuser, '[,]') + 1 and prior id = id and (prior dbms_random.value) is not null) tb2 where tb1.姓名 = tb2.姓名 group by tb1.姓名
借用楼上数据with tt as( select wm_concat(t1.manageuser) mm from test1 t1) select t.姓名, length(t2.mm)-length(replace(t2.mm,t.姓名,'')) 次数 from test t,tt t2
select test.姓名, sum(case when instr(test_1.manageuser,test.姓名)>0 then 1 else 0 end) from test,test_1 group by test.姓名;
count(case when instr(tb2.姓名,tb1.姓名)>1 then 1 else 0 end)
from tb1,tb2
group by tb1.姓名;
count(case when instr(tb2.姓名,tb1.姓名)>0 then 1 else 0 end)
from tb1,tb2
group by tb1.姓名;
错了,是大于0
drop table test;
create table test(姓名 varchar2(20));insert into test values('a');
insert into test values('b');
insert into test values('d');
insert into test values('c');
create table test1(id number(2),manageuser varchar(40));
insert into test1 values(1,'a,b,c');
insert into test1 values(2,'a,b');
insert into test1 values(3,'a,b,c,d');
insert into test1 values(4,'a,c');
select tb1.姓名,count(*)
from test tb1,
(select regexp_substr(manageuser, '[^,]+', 1, level) 姓名
from test1 tb2
connect by level <= regexp_count(manageuser, '[,]') + 1
and prior id = id
and (prior dbms_random.value) is not null) tb2
where tb1.姓名 = tb2.姓名
group by tb1.姓名
select wm_concat(t1.manageuser) mm from test1 t1)
select t.姓名, length(t2.mm)-length(replace(t2.mm,t.姓名,'')) 次数
from test t,tt t2
sum(case when instr(test_1.manageuser,test.姓名)>0 then 1 else 0 end)
from test,test_1
group by test.姓名;