以下查询在几乎每个数据库(提供或获取虚拟表)中都非常有效dual,包括Oracle:
select 'A' as x from dual union all
select 'B' from dual
order by x asc
返回:
| X |
|---|
| A |
| B |
现在这个查询仍然是非常标准的SQL,但不适用于Oracle
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
但是,这有效:
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和第二个(??)subselect需要有一个名为的列x.在第一个例子中,两个子选择似乎只是重合.工作范例:
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
您可能已经猜到了,这不起作用:
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
有趣的附注:
衍生表似乎不受此限制.这有效:
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解析器中的一个(已知?)错误,还是语言语法中有任何非常细微的细节,绝对需要第一个和倒数第二个子选择来保存从该ORDER BY子句引用的名称列?