在子查询Oracle中无法识别表别名

Question

我有一个带有左连接的SELECT语句，连接的表是子查询。Oracle无法识别第二个子查询中第一个子查询的别名。它适用于DB2，但不适用于Oracle。我如何实现它或重写我的查询？

SELECT *                  
 FROM                                                      
 (SELECT     E.C3 AS  COLUMN3        
    , E.C4 AS  COLUMN4        
   FROM TBL_1 D                            
    , TBL_2 E                            
WHERE D.C6  =  E.C6 ) B                                                              
LEFT  JOIN  TABLE   
(SELECT  C.C1                          
 FROM    TBL_3  C                   
 WHERE                                                  
 C.C7            = 'hello'      
 AND B.C3            = C.C8                            
UNION ALL                                                 
 SELECT  C.C1                          
    FROM    TBL_3  C                
    WHERE                                               
    C.C7          = 'world'      
    AND B.C4          =  C.C8                       
    )            A               
ON 1 = 1

Oracle错误消息：ORA-00904：“B”。“C3”：无效标识符

Answer 1

你有一个语法错误。这个:

select * from (select col1 from tbl_Ajoin) A
left join table (select col2 from tbl_Bjoin where A.col1 = tbl_Bjoin.col1) B     
ON 1 = 1

应该是这样的：

select * from (select col1 from tbl_Ajoin) A
left join (select col2 from tbl_Bjoin where A.col1 = tbl_Bjoin.col1) B     
ON 1 = 1

或者更具体地说

left join table (select

不应该有单词表。应该是这样的：

left join (select

Answer 2

您可以将此查询简化为以下内容，删除子查询：

Select      A.Col1, B.Col2
From        tbl_AJoin   A
Left Join   tbl_BJoin   B   On  A.col1 = B.col1