SQL 使用内嵌视图转换数据|极客教程

SQL 使用内嵌视图转换数据，你有一个表，其中一列存放的数据有时是数字，有时是字符。具体存放了什么类型的数据记录在同一张表的另外一列里。你希望使用一个子查询把数字类型的数据都提取出来。

SQL 使用内嵌视图转换数据问题描述

你有一个表，其中一列存放的数据有时是数字，有时是字符。具体存放了什么类型的数据记录在同一张表的另外一列里。你希望使用一个子查询把数字类型的数据都提取出来。

select *
  from ( select flag, to_number(num) num
       from subtest
       where flag in ('A', 'C') )
       where num > 0

不过，上述内嵌视图查询常常返回如下所示的错误信息（但并非总是报错）。

ERROR:
ORA-01722: invalid number

SQL 使用内嵌视图转换数据解决方案

一种办法是强制要求内嵌视图先于外层 SELECT 语句执行。至少在 Oracle 中可以这样做，具体做法是为内层的 SELECT 列表加上伪列 ROWNUM。

select *
  from ( select rownum, flag, to_number(num) num
       from subtest
       where flag in ('A', 'C') )
       where num > 0

我们会在下面的“讨论”部分具体解释该解决方案的原理。

SQL 使用内嵌视图转换数据扩展知识

之所以出现上述错误是因为有时候优化器会合并内层和外层查询。尽管乍一看去似乎应该先执行内层查询，然后再剔除掉非数字 NUM 值，但实际上真正执行的可能是下面的查询。

select flag, to_number(num) num
from subtest
where to_number(num) > 0 and flag in ('A', 'C');

现在我们就能清楚地看到出错的原因了：调用 TO_NUMBER 函数之前，含有非数字 NUM 值的行并没有被预先排除掉。

　数据库系统应该合并子查询和主查询吗？答案取决于我们考虑问题的角度。应该依据关系理论考虑这个问题，还是把遵从 SQL 标准放在第一位？或者依据某个数据库的具体实现方式来考虑这个问题？

本解决方案向内层查询的 SELECT 列表添加了 ROWNUM，这样一来本问题至少在 Oracle 范围内得到了解决。ROWNUM 是一个函数，它会为每一行查询结果返回一个顺序递增的值。这些顺序递增的值，被称为行号（row number）。如果脱离了具体的查询上下文，Oracle 就无法计算出行号。因此，Oracle 必须先执行子查询并实体化其结果集，这样才能为每一行查询结果计算出正确的行号。也因为这个原因，调用 ROWNUM 函数其实就是一种强迫 Oracle 在执行主查询之前先完整执行子查询的技巧（也就是说，这种状况下 Oracle 不允许合并子查询和主查询）。如果你希望在其他数据库上也这么做，不妨研究一下具体的数据库是否有类似 Oracle 的 ROWNUM 这样的功能。