MySQL“有"条款 [英] MySQL "WITH" clause

问题描述

我正在尝试使用MySQL创建带有"WITH"子句的视图

I'm trying to use MySQL to create a view with the "WITH" clause

WITH authorRating(aname, rating) AS
   SELECT aname, AVG(quantity)
   FROM book
   GROUP BY aname

但是MySQL似乎并不支持这一点.

But it doesn't seem like MySQL supports this.

我认为这是非常标准的，并且我确定Oracle支持这一点.无论如何，有没有强迫MySQL使用"WITH"子句?我已经尝试过使用MyISAM和innoDB引擎.这两个都不起作用.

I thought this was pretty standard and I'm sure Oracle supports this. Is there anyway to force MySQL to use the "WITH" clause? I've tried it with the MyISAM and innoDB engine. Both of these don't work.

推荐答案

更新:MySQL 8.0终于获得了通用表表达式的功能，包括递归CTE.

Update: MySQL 8.0 is finally getting the feature of common table expressions, including recursive CTEs.

这是一个宣布它的博客:

Here's a blog announcing it: http://mysqlserverteam.com/mysql-8-0-labs-recursive-common-table-expressions-in-mysql-ctes/

下面是我最初的答案，该答案最初是我于2008年写的.

Below is my earlier answer, which I originally wrote in 2008.

MySQL 5.x不支持使用SQL-99中定义的WITH语法(也称为公用表表达式)的查询.

MySQL 5.x does not support queries using the WITH syntax defined in SQL-99, also called Common Table Expressions.

自2006年1月以来，这一直是MySQL的功能请求: http://bugs .mysql.com/bug.php?id = 16244

This has been a feature request for MySQL since January 2006: http://bugs.mysql.com/bug.php?id=16244

其他支持常用表表达式的RDBMS产品:

Other RDBMS products that support common table expressions:

• Oracle 9i版本2和更高版本:
  http://www.oracle-base.com/articles/misc/with-clause.php
• Microsoft SQL Server 2005和更高版本:
  http://msdn.microsoft.com/en -us/library/ms190766(v = sql.90).aspx
• IBM DB2 UDB 8和更高版本:
  https://www.postgresql.org/docs/current/static/query-with.html
• Sybase 11及更高版本:
  http://dcx.sybase.com/1100/zh/dbusage_en11/commontblexpr-s-5414852.html
• SQLite 3.8.3及更高版本:
  http://sqlite.org/lang_with.html
• HSQLDB:
  http://hsqldb.org/doc/guide/dataaccess-chapt.html# dac_with_clause
• Firebird 2.1及更高版本(第一个支持递归查询的开源DBMS): http://www.firebirdsql.org/file/documentation/release_notes/html/rlsnotes210.html#rnfb210-cte
• H2数据库(但仅 递归):
  http://www.h2database.com/html/advanced.html#recursive_queries

• Oracle 9i release 2 and later:
  http://www.oracle-base.com/articles/misc/with-clause.php
• Microsoft SQL Server 2005 and later:
  http://msdn.microsoft.com/en-us/library/ms190766(v=sql.90).aspx
• IBM DB2 UDB 8 and later:
  http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0000879.htm
• PostgreSQL 8.4 and later:
  https://www.postgresql.org/docs/current/static/queries-with.html
• Sybase 11 and later:
  http://dcx.sybase.com/1100/en/dbusage_en11/commontblexpr-s-5414852.html
• SQLite 3.8.3 and later:
  http://sqlite.org/lang_with.html
• HSQLDB:
  http://hsqldb.org/doc/guide/dataaccess-chapt.html#dac_with_clause
• Firebird 2.1 and later (the first Open Source DBMS to support recursive queries): http://www.firebirdsql.org/file/documentation/release_notes/html/rlsnotes210.html#rnfb210-cte
• H2 Database (but only recursive):
  http://www.h2database.com/html/advanced.html#recursive_queries

其他不支持WITH子句的数据库(截至2014年2月):

Other databases that lack support for the WITH clause (as of February 2014):

• Informix(尽管Informix确实支持Oracle曾经使用的CONNECT BY语法)
  http ://pic.dhe.ibm.com/infocenter/idshelp/v115/index.jsp?topic =％2Fcom.ibm.sqls.doc％2Fids_sqs_2033.htm

• Informix (though Informix does support the CONNECT BY syntax that was once used by Oracle)
  http://pic.dhe.ibm.com/infocenter/idshelp/v115/index.jsp?topic=%2Fcom.ibm.sqls.doc%2Fids_sqs_2033.htm

这篇关于MySQL“有"条款的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！