Oracle嵌套子查询用rownum+order by时外层表标识符无效问题

• 需求为编写SQL查询实现指定产品关联商品的匹配逻辑：单个产品仅返回1条关联商品，优先取最新启用的商品，无启用商品时取最新的禁用商品
• 对接遗留老应用，初始查询禁止使用JOIN语法
• 现有可正常运行的基础查询如下：

from T_PRODUIT pro, T_PRODUIT_PLATEFORME_EXTENDED pre, T_ARTICLE art, T_TAUX_TVA tva
where pro.id_produit = 1330442 
and art.id_article in (select id_article from T_ARTICLE ta where ta.id_produit = pro.id_produit and ta.id_fournisseur = pre.id_fournisseur_article) 
and pro.ID_PRODUIT = pre.ID_PRODUIT
and pre.ID_PRODUIT = art.ID_PRODUIT(+)
and pre.ID_FOURNISSEUR_ARTICLE = art.ID_FOURNISSEUR(+)
and tva.CODE = pro.ID_TVA

尝试通过两层嵌套子查询+rownum实现排序取首条逻辑时，内层子查询无法识别外层主查询的pro、pre表别名，抛出无效标识符错误，存在问题的SQL写法如下：

from T_PRODUIT pro, T_PRODUIT_PLATEFORME_EXTENDED pre, T_ARTICLE art, T_TAUX_TVA tva
    where pro.id_produit = 1330442 
    and art.id_article in (select * from (select id_article from T_ARTICLE ta where ta.id_produit     = pro.id_produit and ta.id_fournisseur = pre.id_fournisseur_article order by ta.actif DESC) where rownum < 2)
    and pro.ID_PRODUIT = pre.ID_PRODUIT
    and pre.ID_PRODUIT = art.ID_PRODUIT(+)
    and pre.ID_FOURNISSEUR_ARTICLE = art.ID_FOURNISSEUR(+)
    and tva.CODE = pro.ID_TVA

• 根因：从(+)外连接语法、rownum用法可判断数据库为Oracle，Oracle关联子查询仅支持一层嵌套的外层别名引用，两层嵌套后内层无法穿透引用外层主查询的表字段。

使用Oracle原生的KEEP聚合语法改写子查询，仅保留一层子查询即可实现排序取首条逻辑，既符合不使用JOIN的要求，也不会出现别名无法识别的问题：

from T_PRODUIT pro, T_PRODUIT_PLATEFORME_EXTENDED pre, T_ARTICLE art, T_TAUX_TVA tva
where pro.id_produit = 1330442 
and art.id_article = (
  SELECT MAX(ta.id_article) KEEP (DENSE_RANK FIRST ORDER BY ta.actif DESC, ta.id_article DESC)
  FROM T_ARTICLE ta 
  WHERE ta.id_produit = pro.id_produit 
    AND ta.id_fournisseur = pre.id_fournisseur_article
)
and pro.ID_PRODUIT = pre.ID_PRODUIT
and pre.ID_PRODUIT = art.ID_PRODUIT(+)
and pre.ID_FOURNISSEUR_ARTICLE = art.ID_FOURNISSEUR(+)
and tva.CODE = pro.ID_TVA

• 写法说明：
  • 子查询仅一层嵌套，可正常引用外层pro、pre表的别名，无标识符错误
  • DENSE_RANK FIRST ORDER BY ta.actif DESC 实现优先取启用商品的逻辑，第二个排序字段ta.id_article DESC实现取最新商品的逻辑，如果业务上以创建时间、更新时间判断新旧，直接将该字段替换为对应时间字段倒序即可
  • MAX(ta.id_article)用于处理极端脏数据导致排序后出现并列第一的场景，保证子查询仅返回1条结果
  • 完全兼容遗留系统规范：未使用JOIN语法，适配原有老版本Oracle的(+)外连接写法

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