Oracle SQL Developer嵌套FROM语句引用别名字段报错问题问询

报错根因 #

• 最内层计算行号的子查询仅选择了table1.*和行号字段rn，关联的table2、table3的字段没有被纳入查询结果集，因此上层x、y子查询的返回字段中根本不存在field1、field2、entity、TYPE1，单独引用时自然触发报错。
• 你期望的y.table2.field1这类写法不符合SQL语法规范：子查询一旦封装完成，外层只能引用子查询最终输出的字段，无法穿透子查询直接访问内层的表别名。

解决方案 #

你只需要将需要用到的关联表字段显式加入最内层查询的选择列表即可，修改后的代码如下：

(
select 
       y.field1 as PRODUCT,
       y.field2 as PRODUCT_TYPE,
       y.entity,
       y.TYPE1
       ,y.*
from   (
  select *
  from   (
    -- 此处新增需要用到的table2、table3字段
    select table1.*,
           table2.field1,
           table2.field2,
           table3.entity,
           table3.TYPE1,
           row_number() over (
             partition by
             table2.field1,
             table2.field2,
             table3.entity,
             table3.TYPE1 
             order by table3.entity
           ) as rn
    from   table1
           inner join table6  on table1.field_1 = table6.code1
           inner join table5  on (table6.code = table5.code1)
                              AND (table6.code = table5.code) 
            left join table3  on table6.ent1 = table3.ent_code
            left join table2  on table1.extid = table2.extID
    where  table1.tdate between '01-APR-19' and '01-APR-21'
    AND    table1.refe NOT IN ('OFF')
  ) x
  where rn <= 2
) y
);

补充说明 #

如果table1中也存在同名的field1、field2等字段，你可以在最内层选择时为关联表字段添加专属别名避免冲突，示例如下：

-- 最内层select部分修改为
select table1.*,
       table2.field1 as t2_field1,
       table2.field2 as t2_field2,
       table3.entity as t3_entity,
       table3.TYPE1 as t3_TYPE1,
       row_number() over (...) as rn
-- 外层引用时直接使用别名即可
select 
       y.t2_field1 as PRODUCT,
       y.t2_field2 as PRODUCT_TYPE,
       y.t3_entity as entity,
       y.t3_TYPE1 as TYPE1
       ,y.*
from ...

这样既可以保证你需要的字段排在结果最前面，也不会出现字段冲突或者找不到的问题。

内容的提问来源于stack exchange，提问作者Kyle Strougo