求SQL

[panku]

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中。

[卖水果的net]

把结果也说一下。

[panku]

之前写过来个SQL，效率太低，SQL如下： select a.cid,sum(num) num from a where not exists (select 1 from b where '|' || b.cid like '%|'||a.id ||'|%' )

[panku]

结果如下： cid num 185 34

[panku]

修改下B表的数据格式 B表 id cid 1 101|102|103|104| 2. 201|223|432|235| 3 102|345|999|234|230|

[学海无涯-回头是岸]

建议把b表的cid，按|拆开，但是效率 也不会高

[可乐乐可]

select t.id,
       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 就给忽略了，所以两边要加上单竖线。

[AHUA1001]

楼上的，我基本赞同你的思路，但是要改一下。
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

[皮面与平面]

--以sql为准
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

[wangfutai91]

create table a (
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);

[AHUA1001]

SELECT SUM(A.NUM) FROM A WHERE EXISTS(SELECT 0 FROM B WHERE '|'||A.CID||'|' LIKE '%'||'|'||B.CID||'|'||'%') ;