SQL> edi 已写入 file afiedt.buf 1 with tb1 as 2 ( 3 select 4 well_id, 5 of_well_id of_well_id1, 6 of_zone_id of_zone_id1, 7 of_zone_seq of_zone_seq1 8 from test3 where of_seq_no='1'), 9 tb2 as 10 (select 11 well_id, 12 of_well_id of_well_id2, 13 of_zone_id of_zone_id2, 14 of_zone_seq of_zone_seq2 15 from test3 where of_seq_no='2') 16 select a.well_id,of_well_id1,of_zone_id1,of_zone_seq1, 17 of_well_id2,of_zone_id2,of_zone_seq2 18 from tb1 a,tb2 b 19* where a.well_id=b.well_id(+) and a.of_zone_seq1=b.of_zone_seq2(+) SQL> /WELL_ID OF_WELL_ID OF_ZONE_ID OF_ZONE_SE OF_WELL_ID OF_ZONE_ID OF_ZONE_SE ---------- ---------- ---------- ---------- ---------- ---------- ---------- A B b2 2 C c1 2 A B b3 3 C c4 3 A B b1 1 C c3 1 A B b4 4
非sql勿扰。
已写入 file afiedt.buf 1 with tb1 as
2 (
3 select
4 well_id,
5 of_well_id of_well_id1,
6 of_zone_id of_zone_id1,
7 of_zone_seq of_zone_seq1
8 from test3 where of_seq_no='1'),
9 tb2 as
10 (select
11 well_id,
12 of_well_id of_well_id2,
13 of_zone_id of_zone_id2,
14 of_zone_seq of_zone_seq2
15 from test3 where of_seq_no='2')
16 select a.well_id,of_well_id1,of_zone_id1,of_zone_seq1,
17 of_well_id2,of_zone_id2,of_zone_seq2
18 from tb1 a,tb2 b
19* where a.well_id=b.well_id(+) and a.of_zone_seq1=b.of_zone_seq2(+)
SQL> /WELL_ID OF_WELL_ID OF_ZONE_ID OF_ZONE_SE OF_WELL_ID OF_ZONE_ID OF_ZONE_SE
---------- ---------- ---------- ---------- ---------- ---------- ----------
A B b2 2 C c1 2
A B b3 3 C c4 3
A B b1 1 C c3 1
A B b4 4
多谢4楼。
不过有点问题想请教。如果of_seq_no 有若干取值 1、2、3....10。
那不就要写10个with 。
然后在10个with 结果做full join .
这样的话sql 个人感觉很复杂了。不知还有更好的方法没?
多谢!
well_id,
max(decode(of_seq_no,'1',of_well_id)) of_well_id1,
max(decode(of_seq_no,'1',of_zone_id)) of_zone_id1,
of_zone_seq,
max(decode(of_seq_no,'2',of_well_id)) of_well_id2,
max(decode(of_seq_no,'2',of_zone_id)) of_zone_id2
from test3
group by well_id,of_zone_seq;这不就结了
你这里主要是要根据of_zone_seq来分组,而不是well_id
有需求自己却没搞清楚对应的逻辑,如何转化成代码?