Oracle UNION和ORDER BY的好奇问题?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (15)

以下查询在几乎每个数据库(提供或采用虚拟表)中都是完全有效的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

我一直在玩弄这个问题,并明白,至少第一个子查询和第二个最后的(??)子查询需要有一个名为的列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子句中引用的名称列?

提问于
用户回答回答于

这并没有真正回答这个问题,但它似乎是一个解析器错误(或'功能')而不是语言要求。

据我的Oracle支持,这似乎已被提出为错误14196463,但没有解决方案关闭。你需要一个MOS账户,或者至少一个Oracle账户,以查看其中的任何一个。

文档有点含糊,但并不表示这是预料之中的问题:

对于包含集合运算符化合物查询UNIONINTERSECTMINUS,或UNION ALL,该ORDER BY子句必须指定位置或别名,而不是显式表达式。此外,ORDER BY子句只能出现在最后一个组件查询中。该ORDER BY子句命令整个复合查询返回的所有行。

你正在混淆你的表达并使用它,并且没有说你必须混淆特定的组件(尽管它当然没有说你不必这样做)。

这种行为似乎与最终预测中的别名不一致,并且关于别名的通常规则仅在order by子句中有效 - 这似乎正在落在其间的某处。

用户回答回答于

这并不能回答你为什么从当前查询中获得不一致的行为,但在Oracle中,你可以轻松地将其重写为以下内容(应始终不会因失败的标识符错误而失败):

with t(x) as (
  select 'A' from dual
  union all
  select 'B' from dual
  union all
  select 'C' from dual
)
select * from t
order by x asc

有了额外的奖励,你只能指定列别名(x)一次。

扫码关注云+社区