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中。
uj5u.com熱心網友回復：
把結果也說一下。uj5u.com熱心網友回復：
結果如下：cid num
185 34
uj5u.com熱心網友回復：
之前寫過來個SQL，效率太低，SQL如下：select a.cid,sum(num) num
from a
where not exists (select 1
from b
where '|' || b.cid like '%|'||a.id ||'|%'
)
uj5u.com熱心網友回復：
修改下B表的資料格式B表
id cid
1 101|102|103|104|
2. 201|223|432|235|
3 102|345|999|234|230|
uj5u.com熱心網友回復：
建議把b表的cid，按|拆開，但是效率 也不會高uj5u.com熱心網友回復：
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
uj5u.com熱心網友回復：
看看我這么寫行不行？select sum(A.NUM) FROM A ,B WHERE INSTR(B.CID,'|'||A.CID||'|')=0 ;
關鍵就是要確定包含字串的唯一性。
避免102|345|999|234|230 結果把99 就給忽略了，所以兩邊要加上單豎線。
uj5u.com熱心網友回復：
樓上的，我基本贊同你的思路，但是要改一下。select sum(A.NUM) FROM A ,B WHERE INSTR('|'||B.CID||'|','|'||A.CID||'|')=0 ;
uj5u.com熱心網友回復：
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
uj5u.com熱心網友回復：
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
uj5u.com熱心網友回復：
--以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
uj5u.com熱心網友回復：
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);
uj5u.com熱心網友回復：
SELECT SUM(A.NUM) FROM A WHERE EXISTS(SELECT 0 FROM B WHERE '|'||A.CID||'|' LIKE '%'||'|'||B.CID||'|'||'%') ;轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/51028.html
標籤：開發
下一篇：CASE WHEN 求平均值