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.08Oracle两张表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请问怎么做到?
(
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.08Oracle两张表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请问怎么做到?
解决方案 »
- UPDATE tablename SET s="locked" WHERE s="unlocked" AND id=x;能有行锁的效果么?
- 9i 2003 群集 思科 ASA-5520-K8 远程连接超时问题
- 如何使用域名?
- oracle9i中怎么用sql语句按汉字拼音先后顺序返回查询结果?
- 初学Oracle求个简单的触发器 在线等
- 请教:安装ORACLE8.17时,为何总要“重试”?
- 如何卸载oracle数据库?
- 各位有谁能详细讲一下,左连接,右连接,内连接,外连接这几个概念,大家一起讨论,来者送分
- 如何根据查询结果决定是否插入数据?
- ROWID的问题!!
- oracle如何将一列更新为另外一列
- sqlplus乱码外加NLS数据文件错误
select ta.*,
(select sum(strb2)
from TEST20180613B tb
where '%,' || ta.stra2 || ',%' like '%,' || tb.strb1 || ',%'
) 交易额
from TEST20180613A ta
order by to_number(ta.stra1)
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);