取两列对应关系比较

 

表1 geowellconnectionwellgroup 数据类似下面：

 

  ID1        ID2

---------- ----------

1500003107 1500003117

1500003109 1500003113

1500003109 1500003115

1500003111 1500003110

1500003112 1500003117

1500003104 1500003103

1500003105 1500003308

1500003109 1500003100

1500003109 1500003110

1500003111 1500003103

1500003112 1500003108

1500003112 1500003116

1500003204 1500003208

 

 

表2 registerwells 数据类似下面

 

 

 

   ID NAME                

---------- --------------------

1500011111 BZ34-2-3D-S-S2      

1500011112 BZ34-2-3D-S-S3      

1500011113 BZ34-2-3D-U-DJ      

1500011114 BZ34-2-4D           

1500011115 BZ34-2-4D-L-S3      

1500011116 BZ34-2-4D-L-S4      

1500011117 BZ34-2-4D-S-S3      

1500011118 BZ34-2-4D-S-S4      

1500011119 BZ34-2-4D-U-DJ      

1500011120 BZ34-2-P1           

1500011121 BZ34-2-P1-E-CM      

1500011122 BZ34-2-P1-L-S2      

1500011123 BZ34-2-P1-L-S3      

1500011124 BZ34-2-P1-S-S2      

1500002219 CB-B-20             

 

 

 

 

目的想得到下面的结果集，在表1中的id对应出name：

 

 

 

       ID1        ID2 WELLNAME             WELLNAME_1          

---------- ---------- -------------------- --------------------

1500003104 1500003103 JZ9-3-E1-6           JZ9-3-E1-5          

1500003105 1500003306 JZ9-3-E1-7           JZ9-3-A07           

1500003105 1500003106 JZ9-3-E1-7           JZ9-3-E1-8          

1500003105 1500003308 JZ9-3-E1-7           JZ9-3-A09s          

1500003107 1500003108 JZ9-3-E2-2           JZ9-3-E2-3          

1500003107 1500003100 JZ9-3-E2-2           JZ9-3-E1-2          

1500003107 1500003113 JZ9-3-E2-2           JZ9-3-E3-4          

1500003107 1500003117 JZ9-3-E2-2           JZ9-3-E2-7          

1500003109 1500003101 JZ9-3-E2-4           JZ9-3-E1-3          

1500003109 1500003108 JZ9-3-E2-4           JZ9-3-E2-3          

1500003109 1500003102 JZ9-3-E2-4           JZ9-3-E1-4          

1500003109 1500003119 JZ9-3-E2-4           JZ9-3-E1-9          

1500003109 1500003117 JZ9-3-E2-4           JZ9-3-E2-7          

 

 

 

 

方案1，取笛卡儿积

 

 

 

SELECT A,D,W1,W2 FROM 

(

select * from (

with g as (

 

SELECT distinct  WELLID AS ID1, CONNECTIONWELLID as id2 

          FROM ogdp.geowellconnectionwellgroup 

 

)

 

select  a,d,w1,w2 from (

select  g.id1 as a ,g.id2 as b ,b.wellname as w1 from g 

join ogdp.registerwells b on g.id1 = b.wellid ) aa

cross join

( 

select  g.id1 as c,g.id2 as d,b.wellname  as w2 from g 

join ogdp.registerwells b on g.id2 = b.wellid )  bb 

 

) F 

,geowellconnectionwellgroup S 

where S.WELLID =F.A AND S.CONNECTIONWELLID = F.D

)

方案2 ：直接做join

with g as (

SELECT distinct  WELLID AS ID1, CONNECTIONWELLID as id2 

          FROM ogdp.geowellconnectionwellgroup  a

)

select first.id1,first.id2,first.wellname,r2.wellname from (

select g.id1,g.id2,r1.wellid,r1.wellname from g

join  ogdp.registerwells  r1 on g.id1 = r1.wellid ) first  

join  ogdp.registerwells  r2 on first.id2 = r2.wellid

方案2效率高些