在查询中使用别名导致“命令未正确结束". [英] Using Alias in query resulting in "command not properly ended"

问题描述

我尝试过:

SELECT *
FROM (SELECT *
           , ROW_NUMBER() OVER (ORDER BY vernum DESC, defvern DESC) AS RowNumber
      FROM   MyTable
             INNER JOIN AnotherTable ON MyTable.id = AnotherTable.dataid
      WHERE  MyTable.defid = 123456 
             AND MyTable.attrid = 10) AS a
WHERE a.RowNumber = 1;

我收到此错误:

ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"
*Cause:    
*Action:
Error at Line: 8 Column: 37

当我删除AS a和WHERE a.RowNumber = 1;时，查询工作正常.

When I remove AS a and the WHERE a.RowNumber = 1; the query works fine.

是否有不能将子查询分配给别名的原因?

Is there a reason I can't assign the subquery to an alias?

推荐答案

Oracle不支持as的表别名.

Oracle does not support table alias with the as.

例如:

SQL> select 1
  2  from dual as a;
from dual as a
             *
ERROR at line 2:
ORA-00933: SQL command not properly ended


SQL> select 1
  2  from dual a;

         1
----------
         1

相同的方式:

SQL> select *
  2  from (
  3        select 1 from dual
  4       ) as a;
     ) as a
          *
ERROR at line 4:
ORA-00933: SQL command not properly ended


SQL> select *
  2  from (
  3        select 1 from dual
  4       )  a;

         1
----------
         1

列别名可以同时包含和不包含as:

Column alias can be both with and without the as:

SQL> select 1 as one, 2 two
  2  from dual;

       ONE        TWO
---------- ----------
         1          2

这篇关于在查询中使用别名导致“命令未正确结束".的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！