oracle with as 提示ORA-02287

如果在使用Oracle的WITH子句时遇到ORA-02287错误，那么很可能是因为在WITH子句中定义了一个具有重复值的列。例如，下面的SQL语句将在执行时引发ORA-02287错误：

WITH mydata AS (
  SELECT 1 as id, 'John' as name from dual
  UNION ALL
  SELECT 2 as id, 'Jane' as name from dual
  UNION ALL
  SELECT 1 as id, 'Peter' as name from dual
)
SELECT * FROM mydata;

在上述SQL语句中，第一个SELECT语句和第二个SELECT语句返回的记录id是唯一的，但第三个SELECT语句返回的记录具有一个与第一个记录相同的id值，这将导致ORA-02287错误。

要解决这个问题，您需要确保WITH子句中定义的列具有唯一的值。在上述例子中，您可以通过更改第三个SELECT语句中的id值来解决问题，如下所示：

WITH mydata AS (
  SELECT 1 as id, 'John' as name from dual
  UNION ALL
  SELECT 2 as id, 'Jane' as name from dual
  UNION ALL
  SELECT 3 as id, 'Peter' as name from dual
)
SELECT * FROM mydata;

这样，每个记录的id值都是唯一的，不会再出现ORA-02287错误。