时间戳

首页 > 解决方案 > 即使列有前缀，“列定义不明确的错误”？

oracle - 即使列有前缀，“列定义不明确的错误”？

问题描述

我有以下 sql，它工作正常（Oracle Database 12c Enterprise Edition Release 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: 列定义不明确

  1. 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

更新2：

以下可测试查询出现错误。

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

标签： oracleoracle12c

解决方案

我发现重命名共享的列名high并且“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 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

但这对我（问题所有者）来说真的不是一个解决方案。代码会动态生成，会有很多列。

当多个子查询返回具有相同名称的列并且这些列位于 select 子句中时，Oracle 似乎存在问题。

解决方案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也将摆脱错误。

推荐阅读