For SQL-CodePudding

A table
Cid num
30 101
102 34
185 34
230, 224,
12 345
.

B
Id cid
1 101 | 102 | 103 | 104
2. 201 | 223 | 432 | 235
3 102 | 345 | 999 | 234 | 230

A num sum, the condition of A table, A table of cid is not in B some cid,

CodePudding user response:

The results also said,

CodePudding user response:

The results are as follows:
Cid num
185 34

CodePudding user response:

CodePudding user response:

Modify the B table data format
Table B
Id cid
1 101 | 102 | 103 | 104 |
2. 201 | 223 | | 432 | 235
3 102 | 345 | 999 | | 234 | 230

CodePudding user response:

Suggested that the b table cid, press | apart, but the efficiency is not high

CodePudding user response:

 select t.i d, 
Tc id, 
(select sum (a1) num) from a1 where tc id like '%' | | a1. Cid | | '%') num 
The from b1 t;

1 101 | 102 | 103 | 104 64
2 201 | 223 | 432 | 235
3 102 | 345 | | 234 | 230 270 999

CodePudding user response:

Look at me so write a line not line?
Select sum (A.N UM) FROM A, B WHERE INSTR (biggest ID, '|' | | A.C ID | | '|')=0.
The key is to determine the uniqueness of contain the string,
Avoid 102 | 345 | 999 | 234 | 230 to 99 to ignore, so both sides with a single vertical bar,

CodePudding user response:

Upstairs, I basic agree with your ideas, but want to change,
Select sum (A.N UM) FROM A, B WHERE INSTR (' | | | biggest ID | | '|', '| | | A.C ID | |' | ')=0.

CodePudding user response:

Select cid, sum (num) as num from TB_A where cid not in (
The select v_cid from (
Select distinct level,
Cid,
Regexp_substr (tc id, '[^ |] +', 1, level) v_cid
The from TB_b t
Connect by level & lt;=length (tc id) - length (the replace (tc id, '|', ')) + 1)
)
Group by cid

CodePudding user response:

Select cid, sum (num) as num
The from TB_A
Where cid not in (
The select regexp_substr (cid, '[^ |] +', 1, rn) TB_b
The from (select t1. Cid, t2. Rn
The from (select cid,
Length (cid) - length (the replace (cid, '|', ')) + 1 rn
The from TB_b) t1,
(select level rn
The from dual
Connect by rownum & lt;=
(select Max (length (cid) -
Length (the replace (cid, '|', ')) + 1) rn
The from t2 TB_b))
Where a t1. An rn & gt;=t2. Rn
The order by cid, rn)

)
Group by cid

CodePudding user response:

- will be subject to SQL
Select cid, sum (num) as num
The from TB_A
Where cid not in (
The select regexp_substr (cid, '[^ |] +', 1, rn) cid
The from (select t1. Cid, t2. Rn
The from (select cid,
Length (cid) - length (the replace (cid, '|', ')) + 1 rn
The from TB_b) t1,
(select level rn
The from dual
Connect by rownum & lt;=
(select Max (length (cid) -
Length (the replace (cid, '|', ')) + 1) rn
The from t2 TB_b))
Where a t1. An rn & gt;=t2. Rn
The order by cid, rn)

)
Group by cid

CodePudding user response:

The create table a (
Cid number,
Num number);
Drop table b;
The create table b (
Id number,
Cid varchar2 (100));

With TMP as
(select id, regexp_substr (cid, '[^ |] +', 1, level) new_cid
The from b
Connect by level & lt;=regexp_count (cid, '|') + 1
And the prior rowid=rowid
Value is not null and the prior dbms_random.
And regexp_substr (cid, '[^ |] +', 1, level) is not null)
Select * from a where clause a.c id not in (select new_cid from TMP);

CodePudding user response:

SELECT SUM (A.N UM) FROM A WHERE the EXISTS (SELECT 0 FROM B WHERE '| | | A.C ID | |' | 'LIKE' % '| |' | | | biggest ID | | '| | |' % ');