下面的查询在几乎每个数据库(或多或少一个dual
虚拟表)中都是perfectly valid的,包括Oracle:
select 'A' as x from dual union all
select 'B' from dual
order by x asc
正在返回:
| X |
|---|
| A |
| B |
现在,这个查询仍然是非常标准的SQL,但是在Oracle上使用doesn't work
select 'A' as x from dual union all
select 'B' from dual union all
select 'C' from dual
order by x asc
我得到了
ORA-00904: "X": invalid identifier
然而,这是works
select 'A' as x from dual union all
select 'B' as x from dual union all
select 'C' from dual
order by x asc
我一直在处理这个问题,并弄清楚,显然,至少第一次选择和倒数第二次选择(??)subselect需要有一个名为x
的列。在第一个示例中,两个子选择似乎简单地重合。Working example
select 'A' as x from dual union all
select 'B' from dual union all
select 'C' from dual union all
select 'D' from dual union all
select 'E' from dual union all
select 'F' as x from dual union all
select 'G' from dual
order by x asc
正如您可能已经猜到的,这个wouldn't work
select 'A' as x from dual union all
select 'B' from dual union all
select 'C' from dual union all
select 'D' from dual union all
select 'E' as x from dual union all
select 'F' from dual union all
select 'G' from dual
order by x asc
有趣的附注:
派生表似乎不受此限制。This works
select * from (
select 'A' as x from dual union all
select 'B' from dual union all
select 'C' from dual
)
order by x asc
问题:
这是一个(已知的?)Oracle SQL解析器中是否存在错误,或者在语言语法中是否有任何非常细微的细节绝对要求第一个和倒数第二个subselect保存与ORDER BY
子句中引用的名称相同的列?
https://stackoverflow.com/questions/25387951
复制相似问题