select 点号1,t2.编号,t1.点号2,t3.编号 from table1 t1,table2 t2,table2 t3 where t1.点号1 = t2.点号 and t1.点号2 = t3.点号 你这样试一下吧
你看这样能不能达到您的要求:select a.点号1, case when a.点号1 is not null then c.编号 else null end 点号1的编号, b.点号2, case when b.点号2 is not null then c.编号 else null end 点号2的编号 from table1 a,table1 b,table2 c where c.点号 = a.点号1(+) and c.点号 = b.点号2(+)
楼主需要的结果 点号1,点号1的编号,点号2,点号2的编号而上面大侠们实现的结果会成为点号1,点号1的编号,NULL,NULL NULL,NULL,点号2,点号2的编号所以还需要将这两行合并为一行就可以了SELECT ppp,MAX(点号1),MAX(编号),MAX(点号2),MAX(编号2) FROM ( SELECT a.点号1 as ppp ,A.点号1,B.编号,null as 点号2 ,null as 编号 FROM A LEFT JOIN B ON A.点号1=B.点号 UNION ALL SELECT a.点号1 as ppp, null,null,A.点号2,B.编号 FROM A LEFT JOIN B ON A.点号2=B.点号) S GROUP BY ppp
from table1 t1,table2 t2,table2 t3
where t1.点号1 = t2.点号
and t1.点号2 = t3.点号
你这样试一下吧
case when a.点号1 is not null then c.编号 else null end 点号1的编号,
b.点号2,
case when b.点号2 is not null then c.编号 else null end 点号2的编号
from table1 a,table1 b,table2 c
where c.点号 = a.点号1(+)
and c.点号 = b.点号2(+)
NULL,NULL,点号2,点号2的编号所以还需要将这两行合并为一行就可以了SELECT ppp,MAX(点号1),MAX(编号),MAX(点号2),MAX(编号2)
FROM (
SELECT a.点号1 as ppp ,A.点号1,B.编号,null as 点号2 ,null as 编号
FROM A
LEFT JOIN B ON A.点号1=B.点号
UNION ALL
SELECT a.点号1 as ppp, null,null,A.点号2,B.编号
FROM A
LEFT JOIN B ON A.点号2=B.点号) S
GROUP BY ppp