A表
cid num
101 30
102 34
185 34
230 224
345 12
...
B有
id cid
1 101|102|103|104
2. 201|223|432|235
3 102|345|999|234|230求A表的num之和,条件:A表的cid不在B有的cid中。
cid num
101 30
102 34
185 34
230 224
345 12
...
B有
id cid
1 101|102|103|104
2. 201|223|432|235
3 102|345|999|234|230求A表的num之和,条件:A表的cid不在B有的cid中。
cid num
185 34
select a.cid,sum(num) num
from a
where not exists (select 1
from b
where '|' || b.cid like '%|'||a.id ||'|%'
)
B表
id cid
1 101|102|103|104|
2. 201|223|432|235|
3 102|345|999|234|230|
t.cid,
(select sum(a1.num) from a1 where t.cid like '%' || a1.cid || '%') num
from b1 t;
1 101|102|103|104 64
2 201|223|432|235
3 102|345|999|234|230 270
select sum(A.NUM) FROM A ,B WHERE INSTR(B.CID,'|'||A.CID||'|')=0 ;
关键就是要确定包含字符串的唯一性。
避免102|345|999|234|230 结果把99 就给忽略了,所以两边要加上单竖线。
select sum(A.NUM) FROM A ,B WHERE INSTR('|'||B.CID||'|','|'||A.CID||'|')=0 ;
select cid,sum(num) as num from TB_A where cid not in (
select v_cid from (
select distinct level,
cid,
regexp_substr(t.cid, '[^|]+', 1, level) v_cid
from TB_b t
connect by level <= length(t.cid) - length(replace(t.cid, '|', '')) + 1)
)
group by cid
select cid, sum(num) as num
from TB_A
where cid not in (
select regexp_substr(cid, '[^|]+', 1, rn) TB_b
from (select t1.cid, t2.rn
from (select cid,
length(cid) - length(replace(cid, '|', '')) + 1 rn
from TB_b) t1,
(select level rn
from dual
connect by rownum <=
(select max(length(cid) -
length(replace(cid, '|', '')) + 1) rn
from TB_b)) t2
where t1.rn >= t2.rn
order by cid, rn)
)
group by cid
select cid, sum(num) as num
from TB_A
where cid not in (
select regexp_substr(cid, '[^|]+', 1, rn) cid
from (select t1.cid, t2.rn
from (select cid,
length(cid) - length(replace(cid, '|', '')) + 1 rn
from TB_b) t1,
(select level rn
from dual
connect by rownum <=
(select max(length(cid) -
length(replace(cid, '|', '')) + 1) rn
from TB_b)) t2
where t1.rn >= t2.rn
order by cid, rn)
)
group by cid
cid number,
num number);
drop table b;
create table b (
id number,
cid varchar2(100));
with tmp as
(select id, regexp_substr(cid, '[^|]+', 1, level) new_cid
from b
connect by level <= regexp_count(cid, '|') + 1
and prior rowid = rowid
and prior dbms_random.value is not null
and regexp_substr(cid, '[^|]+', 1, level) is not null)
select * from a where a.cid not in (select new_cid from tmp);