Oracle-二级子查询无法看到主查询中的字段 [英] Oracle - Second level subquery cannot see field from main query

问题描述

在Oracle 12c中，以下查询有效，但在Oracle 11g中则无效，因为第二级子查询无法看到主查询中的字段.
如何重写查询以使其也可在Oracle 11中使用?

In Oracle 12c the following query works, but not in Oracle 11g, because second level subquery cannot see field from main query.
How can I rewrite the query to work in Oracle 11 too?

SELECT   lvl, 
         unique_code, 
         (
            SELECT   unique_code
            FROM     (
                        SELECT   p.unique_code
                        FROM     tree p
                        WHERE    p.lvl = t.lvl - 1
                             AND p.unique_code < t.unique_code
                        ORDER BY p.unique_code DESC
                     )
            WHERE    ROWNUM = 1
         ) AS parent_unique_code
FROM     tree t     

树包含以下行:
(请不要依赖于唯一代码的格式/结构，实际上这是非常复杂的.)

Tree contains rows as follows:
(Please don't rely on the format/structure of the unique code, it is much complex in reality.)

lvl         unique_code
-----------------------
1           A
2           A/X
2           A/Y
3           A/Y/T
2           A/Z
1           B
2           B/X

预期结果如下:

lvl         unique_code   parent_unique_code
--------------------------------------------
1           A                      
2           A/X           A
2           A/Y           A
3           A/Y/T         A/Y
2           A/Z           A
1           B             
2           B/X           B

谢谢

推荐答案

也许您在嵌套查询中只需要max:

Maybe you only need a max in your nested query:

SELECT   lvl, 
         unique_code, 
         (
            SELECT   max(p.unique_code)
                        FROM     tree p
                        WHERE    p.lvl = t.lvl - 1
                             AND p.unique_code < t.unique_code
         ) AS parent_unique_code FROM     tree t

这篇关于Oracle-二级子查询无法看到主查询中的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！