即使列有前缀，"column ambiguously defined error"？

Question

i具有以下SQL，并且工作正常(Oracle Database 12C Enterprise Edition版本12.1.0.2.0-64bit Production(。

with n as (
    select ntile(cnt) over ( partition by cnt order by Id ) NT, Id
    from t
)
select  n.NT, high.Id
from    (select distinct NT from n) n 
        outer apply (
           select * 
           from n x where x.NT = n.NT and rownum = 1 order by Id
        ) low
        outer apply (
           select * 
           from (select * from n x where x.NT = n.NT order by Id desc) d where rownum = 1
        ) high

现在我将其更改为以下内容，

with n as (
    select ntile(cnt) over ( partition by cnt order by Id ) NT, Id
    from t
)
select  n.NT, phigh.Id -- Error - phigh.Id
from    (select distinct NT from n) n 
        outer apply (
            select * 
            from n x where x.NT = n.NT and rownum = 1 order by Id
        ) low
        outer apply (
            select * 
            from (select * from n x where x.NT = n.NT order by Id desc) d where rownum = 1
        ) high
        outer apply (
            select * 
            from (select * from n x where x.NT = n.NT-1 order by Id desc) d where rownum = 1
        ) phigh

现在，它在phigh.Id上遇到以下错误：

ora-00918：列含糊不清

918. 00000 - "列"含糊不清

如果我将phigh.Id更改为phigh.*，则第二个查询有效吗？将phigh.Id更改为high.Id也有效。

更新：

尝试使用CTE的不同别名。仍然有相同的错误

with n as (
    select ntile(cnt) over ( partition by cnt order by Id ) NT, Id
    from t
)
select  n.NT, phigh.Id -- Error - phigh.Id
from    (select distinct NT from n) nt 
        outer apply (
            select * 
            from n x where x.NT = nt.NT and rownum = 1 order by Id
        ) low
        outer apply (
            select * 
            from (select * from n x where x.NT = nt.NT order by Id desc) d where rownum = 1
        ) high
        outer apply (
            select * 
            from (select * from n x where x.NT = nt.NT-1 order by Id desc) d where rownum = 1
        ) phigh

Update2：

以下可测试查询有错误。

with t as (
  select 10 as cnt, 1 as Id from dual
), 
x as (
    select ntile(cnt) over ( partition by cnt order by Id ) NT, Id
    from t
)
select  n.NT, 
        high.Id a, 
        phigh.Id
from    (select distinct NT from x) n 
        outer apply (select * from x where x.NT = n.NT and rownum = 1 order by Id) low
        outer apply (select * from (select * from x where x.NT = n.NT order by Id desc) d where rownum = 1) high
        outer apply (select * from (select * from x where x.NT = n.NT - 1 order by Id desc) d where rownum = 1) phigh

以下代码有效。

with t as (
  select 10 as cnt, 1 as Id from dual
), 
x as (
    select ntile(cnt) over ( partition by cnt order by Id ) NT, Id
    from t
)
select  n.NT, 
        --high.Id a, 
        phigh.Id
from    (select distinct NT from x) n 
        outer apply (select * from x where x.NT = n.NT and rownum = 1 order by Id) low
        --outer apply (select * from (select * from x where x.NT = n.NT order by Id desc) d where rownum = 1) high
        outer apply (select * from (select * from x where x.NT = n.NT - 1 order by Id desc) d where rownum = 1) phigh

Answer 1

您应该在主查询和CTE中使用不同的别名，现在它们是同一n：

with n_cte as (
    select /*+ materialize*/ ntile(cnt) over ( partition by cnt order by Id ) NT, Id
    from tab
)
select  n.NT, phigh.Id 
from    (select distinct NT from n_cte) n 
outer apply (
    select * 
    from n_cte where n.NT = n_cte.NT and rownum = 1 order by Id
) low
outer apply (
    select * 
    from (select * from n_cte where n_cte.NT = n.NT order by Id desc) d where rownum = 1
) high
outer apply (
    select * 
    from (select * from n_cte where n_cte.NT = n.NT-1 order by Id desc) d where rownum = 1
) phigh

db＆lt;>小提琴演示

另一个选项可能是在CTE部分内添加SELECT /*+ materialize*/ ...提示。

Answer 2

我发现重命名high和'Phigh'Works中共享的列名。

with t as (
  select 10 as cnt, 1 as Id from dual
), 
x as (
    select ntile(cnt) over ( partition by cnt order by Id ) NT, Id
    from t
)
select  n.NT, 
        high.Id a, 
        phigh.Id
from    (select distinct NT from x) n 
        outer apply (select * from x where x.NT = n.NT and rownum = 1 order by Id) low
        outer apply (select Id z from (select * from x where x.NT = n.NT order by Id desc) d where rownum = 1) high
        outer apply (select * from (select * from x where x.NT = n.NT - 1 order by Id desc) d where rownum = 1) phigh

，但这确实不是我的解决方案(问题所有者(。该代码将是动态生成的，并且会有许多列。

当多个子Queries返回具有相同名称的返回列，这些列在SELECT子句中。

解决方案2：

将outer apply (select * from x where x.NT = n.NT and rownum = 1 order by Id) low更改为outer apply (select * from x where x.NT = 1 and rownum = 1 order by Id) low也将摆脱错误。