Oracle单SQL合并两个子查询并内连接的技术求助

需求背景 #

习惯用SAS写代码（可创建中间表），现在需要在Oracle SQL Developer中用单条SQL语句完成任务：将两个查询通过INNER JOIN合并，取共同行及变量作为最终输出。

原查询语句 #

Query 1 #

select A.*, B.var1, B.var2, B.var3, B.var4, B.var5, B.var6 as new_var6, B.var7,
    case
        when a.var1 = 'thing' then CONCAT(B.var4,A.var1)
        else CONCAT(b.var7,A.var1)
    end as key_second_join,
    1 as var8
from bibliotheque.BASE_A A
inner join bibliotheque.BASE_B B
on A.key_first_join = B.var1
order by A.key_first_join;

Query 2 #

SELECT T.*, CONCAT(C_VAR1,C_VAR3) key_second_join
FROM (
    SELECT *
        FROM bibliotheque2.BASE_C
        WHERE (C_VAR1, C_VAR2) IN (
            SELECT C_VAR1, MAX(C_VAR2)
            FROM bibliotheque2.BASE_C
            GROUP BY C_VAR1)) T;

预期逻辑 #

尝试按以下逻辑整合，但频繁出现missing right parenthesis、column ambiguously defined错误：

select *
from query1 as t2
inner join query2 as t1 on t1.key_second_join=t2.key_second_join;
run;

补充说明及当前代码 #

已采用WITH子句方案，修改了SELECT子句所有表前缀，但仍遇ORA-00918错误，当前代码如下：

WITH QUERY1 AS (
    SELECT 
    A.VARA1,
    A.VARA2,
    A.VARA3,
    A.VARA4,
    A.VAR5,
    A.VARA6,
    A.VARA7,
    A.VARA8,
    A.VARA9,
    B.VARB1,
    B.VARB2,
    B.VARB3,
    B.VARB4, 
    B.VAR5,
    CASE
        WHEN A.VARA2 = '11307' THEN CONCAT(B.VARB3,A.VARA2)
        ELSE CONCAT(B.VARB4,A.VARA2)
    END AS KEY_SECOND_JOIN_T1,
    1 as VAR10
    FROM BIBLIOTHEQUE.BASE_A A
        INNER JOIN BIBLIOTHEQUE.BASE_B B
        ON A.VAR5 = B.VAR5
        /*ORDER BY A.CT_CAUTION*/
),
QUERY2 AS (
    SELECT 
    C.VARC1,
    C.VARC2,
    C.VARC3,
    C.VARC4,
    CONCAT(VARC1,VARC2) KEY_SECOND_JOIN_T2
    FROM BIBLIOTHEQUE2.BASE_C C
    WHERE(VARC1, VARC3)
        IN(
            SELECT D.VARC1, MAX(D.VARC3)
            FROM BIBLIOTHEQUE2.BASE_C D
            GROUP BY VARC1)
)
SELECT 
    T1.VAR10,
    T1.KEY_SECOND_JOIN_T1,
    T1.VARA1,
    T1.VARA2,
    T1.VARA3,
    T1.VARA4,
    T1.VAR5,
    T1.VARA6,
    T1.VARA7,
    T1.VARA8,
    T1.VARA9,
    T1.VARB1, 
    T1.VARB2,
    T1.VARB3,
    T1.VARB4, 
    T2.KEY_SECOND_JOIN_T2,
    T2.VARC1,
    T2.VARC2,
    T2.VARC3,
    T2.VARC4
    FROM QUERY1 T1
        INNER JOIN QUERY2 T2
        ON T1.KEY_SECOND_JOIN_T1=T2.KEY_SECOND_JOIN_T2
;

问题排查与解决 #

错误原因 #

1. ORA-00918：列定义不明确：QUERY1的SELECT子句中同时选中A.VAR5和B.VAR5，且都用VAR5作为列名，Oracle无法区分最终SELECT中T1.VAR5的来源。
2. QUERY2中部分列引用无表前缀：CONCAT(VARC1,VARC2)、GROUP BY VARC1未指定表别名，虽当前无歧义，但不符合语法规范，可能引发解析问题。
3. QUERY2的WHERE子句格式错误：WHERE(VARC1, VARC3)中WHERE与括号间无空格，Oracle语法解析时会误判。

修正后的代码 #

WITH QUERY1 AS (
    SELECT 
    A.VARA1,
    A.VARA2,
    A.VARA3,
    A.VARA4,
    A.VAR5 AS A_VAR5, -- 重命名消除列名冲突
    A.VARA6,
    A.VARA7,
    A.VARA8,
    A.VARA9,
    B.VARB1,
    B.VARB2,
    B.VARB3,
    B.VARB4, 
    B.VAR5 AS B_VAR5, -- 重命名消除列名冲突
    CASE
        WHEN A.VARA2 = '11307' THEN CONCAT(B.VARB3,A.VARA2)
        ELSE CONCAT(B.VARB4,A.VARA2)
    END AS KEY_SECOND_JOIN_T1,
    1 AS VAR10
    FROM BIBLIOTHEQUE.BASE_A A
        INNER JOIN BIBLIOTHEQUE.BASE_B B
        ON A.VAR5 = B.VAR5
),
QUERY2 AS (
    SELECT 
    C.VARC1,
    C.VARC2,
    C.VARC3,
    C.VARC4,
    CONCAT(C.VARC1,C.VARC2) KEY_SECOND_JOIN_T2 -- 添加表前缀
    FROM BIBLIOTHEQUE2.BASE_C C
    WHERE (C.VARC1, C.VARC3) -- WHERE与括号间加空格，添加表前缀
        IN(
            SELECT D.VARC1, MAX(D.VARC3)
            FROM BIBLIOTHEQUE2.BASE_C D
            GROUP BY D.VARC1 -- GROUP BY添加表前缀
        )
)
SELECT 
    T1.VAR10,
    T1.KEY_SECOND_JOIN_T1,
    T1.VARA1,
    T1.VARA2,
    T1.VARA3,
    T1.VARA4,
    T1.A_VAR5, -- 使用重命名后的列名
    T1.B_VAR5, -- 使用重命名后的列名
    T1.VARA6,
    T1.VARA7,
    T1.VARA8,
    T1.VARA9,
    T1.VARB1, 
    T1.VARB2,
    T1.VARB3,
    T1.VARB4, 
    T2.KEY_SECOND_JOIN_T2,
    T2.VARC1,
    T2.VARC2,
    T2.VARC3,
    T2.VARC4
FROM QUERY1 T1
    INNER JOIN QUERY2 T2
    ON T1.KEY_SECOND_JOIN_T1 = T2.KEY_SECOND_JOIN_T2
;

关键修正点 #

• 对QUERY1中重复的VAR5列分别重命名，彻底消除列名歧义。
• 为QUERY2中所有列引用添加表前缀，严格遵循Oracle语法规范。
• 修正QUERY2中WHERE与括号的空格问题，避免语法解析错误。

内容的提问来源于stack exchange，提问作者J H