Oracle query problem-CodePudding

The create table TEST20180613A
(
Stra1 VARCHAR2 (32),
Stra2 VARCHAR2 (32)
);

The create table TEST20180613B
(
Strb1 VARCHAR2 (32),
Strb2 VARCHAR2 (32)
);

Insert into TEST20180613A (STRA1, STRA2) values (' 1 ', 'A, B);
Insert into TEST20180613A (STRA1, STRA2) values (' 2 ', 'B, C, D);
Insert into TEST20180613A (STRA1 STRA2) values (' 3 ', 'A');
Insert into TEST20180613A (STRA1 STRA2) values (' 4 ', 'C');
Insert into TEST20180613A (STRA1, STRA2) values (' 5 ', 'B, C);
Insert into TEST20180613A (STRA1, STRA2) values (' 6 ', 'C, D);
Insert into TEST20180613A (STRA1, STRA2) values (' 7 ', 'D, E, F, G, H');
Insert into TEST20180613A (STRA1 STRA2) values (' 8 ', 'E');
Insert into TEST20180613A (STRA1, STRA2) values (' 9 ', 'F, G, H, I, J, K, L, M');
Insert into TEST20180613A (STRA1 STRA2) values (' 10 ', 'G');
Insert into TEST20180613A (STRA1, STRA2) values (' 11 ', 'H, I);
Insert into TEST20180613A (STRA1 STRA2) values (' 12 ', 'I');
Insert into TEST20180613A (STRA1 STRA2) values (' 13 ', 'J');
Insert into TEST20180613A (STRA1 STRA2) values (' 14 'and' K ');
Insert into TEST20180613A (STRA1, STRA2) values (' 15 ', 'E, J');
Insert into TEST20180613A (STRA1, STRA2) values (' 16 ', 'A, H);
Insert into TEST20180613A (STRA1, STRA2) values (' 17 ', 'C, E);
Insert into TEST20180613A (STRA1, STRA2) values (' 18 ', 'E, C);
Insert into TEST20180613A (STRA1, STRA2) values (' 19 ', 'B, A');
Insert into TEST20180613A (STRA1, STRA2) values (' 20 ', 'A, B);

Insert into TEST20180613B (STRB1 STRB2) values (' A ', '0.01');
Insert into TEST20180613B (STRB1 STRB2) values (' B ', '0.02');
Insert into TEST20180613B (STRB1 STRB2) values (' C ', '0.03');
Insert into TEST20180613B (STRB1 STRB2) values (' D ', '0.04');
Insert into TEST20180613B (STRB1 STRB2) values (' F ', '0.05');
Insert into TEST20180613B (STRB1 STRB2) values (' H ', '0.06');
Insert into TEST20180613B (STRB1 STRB2) values (' J ', '0.07');
Insert into TEST20180613B (STRB1 STRB2) values (' I ', '0.08');

Table 1: TEST20180613A
Stra1 stra2
1 A, B
2 B, C, D
3 A
4 C
5 B, C
6 C, D
7 D, E, F, G, H
8 E
9 F, G, H, I, J, K, L, M
10 G
11 H, I
12 I
13 J
14 K
15 E, J
16 A, H
17 C, E
18 E, C
19 B, A
20 A, B

Table 2: TEST20180613B
Strb1 strb2
A 0.01
0.02 B
C 0.03
D 0.04
F 0.05
H 0.06
J 0.07
I 0.08

Oracle two tables A and B, now want to export data, contains A table of STRA1 (sort), STRA2 (merchants) and table B STRB2 (volume), through A STRA2 and B STRB1 link two tables, the result like this:

Ordering merchants volumes,
1 A, B
0.032 B, C, D 0.09
3 A 0.01
4 C 0.03
5 B, C 0.05
6 C, D 0.07
7 D, E, F, G, H 0.15
8 E
9 F, G, H, I, J, K, L, M 0.26
10 G
11 H, I 0.14
12 I 0.08
13 J 0.07
14 K
15 E, J
0.0716 A, H 0.07
17 C, E 0.03
18 E, C 0.03
19 B, A 0.03
20 A, B
0.03
How can I do?

CodePudding user response:

 
Select ta. *, 
(select sum (strb2) 
The from TEST20180613B TB 
Where '%,' | | ta.621 stra2 | | '%' like '%,' | | TB. Strb1 | | '%' 
Volumes) 
The from TEST20180613A ta 
The order by to_number (ta) stra1)

CodePudding user response:

Unwrapped A table star2 press, advice, and then linked to B table summation, and group by A.s tar1

CodePudding user response:

 select A.s tra1, A.s tra2, sum (B.s trb2) strb2 
The from TEST20180613A A left join TEST20180613B B 
On (instr (A.s tra2, B.s trb1) & gt; 0) 
Group by A.s tra1, A.s tra2 
The order by to_number (A.s tra1);