create table TEST20180613A
(
stra1 VARCHAR2(32),
stra2 VARCHAR2(32)
);
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', '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');
表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
表2：TEST20180613B
strb1 strb2
A 0.01
B 0.02
C 0.03
D 0.04
F 0.05
H 0.06
J 0.07
I 0.08
Oracle兩張表A和B，現在想要匯出資料，包含A表的STRA1(排序)、STRA2(商戶號)和B表的STRB2(交易額)，通過A的STRA2和B的STRB1關聯兩張表，結果這樣：
排序 商戶號 交易額
1 A,B 0.03
2 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.07
16 A,H 0.07
17 C,E 0.03
18 E,C 0.03
19 B,A 0.03
20 A,B 0.03
請問怎么做到？
uj5u.com熱心網友回復：
select ta.*,
(select sum(strb2)
from TEST20180613B tb
where '%,' || ta.stra2 || ',%' like '%,' || tb.strb1 || ',%'
) 交易額
from TEST20180613A ta
order by to_number(ta.stra1)
uj5u.com熱心網友回復：
建議把A表的star2按，拆開，然后與B表關聯求和，并group by A.star1uj5u.com熱心網友回復：
select A.stra1, A.stra2,sum(B.strb2) strb2
from TEST20180613A A left join TEST20180613B B
on (instr(A.stra2,B.strb1)>0)
group by A.stra1, A.stra2
order by to_number(A.stra1);
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/65082.html
標籤：開發
下一篇：圖書館管理系統里的一個小問題