Oracle AS关键字和子查询 [英] Oracle AS keyword and subqueries

问题描述

当您使用AS关键字为子查询添加别名时，刚发现Oracle不喜欢它:

Just found out that Oracle does not like it when you use the AS keyword to alias a subquery:

SELECT * FROM (SELECT * FROM products) AS p

我需要使我的SQL查询尽可能地可移植.在上面的查询中删除AS关键字是否会影响其他RDBMS?

I need to keep my SQL queries as portable as possible. Will the removal of the AS keyword in the above query affect any other RDBMS?

推荐答案

SQL 99 ANSI的模式是表可以具有别名 WITHOUT AS关键字，因此，您可以采用AS，它应该在每个RDBMS上都可以工作.在小提琴上看到它:

The pattern for the SQL 99 ANSI is that the table can have an alias WITHOUT the AS keyword so, you can take out AS and it should work on every RDBMS. See it on fiddle:

• MySQL
• Oracle
• PostgreSql
• SQLLite
• SQLServer

• MySQL
• Oracle
• PostgreSql
• SQLLite
• SQLServer

在 ISO/IEC 9075-2:1999 中， 7.6<表格参考> ，第232页:

<table reference> ::=
   <table primary>
   | <joined table>

<table primary> ::=
   <table or query name> [ [ AS ] <correlation name>
      [ <left paren> <derived column list> <right paren> ] ]
   | <derived table> [ AS ] <correlation name>
      [ <left paren> <derived column list> <right paren> ]
   | <lateral derived table> [ AS ] <correlation name>
      [ <left paren> <derived column list> <right paren> ]
   | <collection derived table> [ AS ] <correlation name>
      [ <left paren> <derived column list> <right paren> ]
   | <only spec>
      [ [ AS ] <correlation name>
         [ <left paren> <derived column list> <right paren> ] ]
   | <left paren> <joined table> <right paren>

---

也确认可以正常工作:

---

Also confirmed to work:

• MS Access(Jet)

这篇关于Oracle AS关键字和子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！