MySQL“与"条款

2021-11-20 00:00:00 sql common-table-expression mysql

我正在尝试使用 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.

这是一个宣布它的博客:http://mysqlserverteam.com/mysql-8-0-labs-recursive-common-table-expressions-in-mysql-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 及更高版本:
    http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0000879.htm
  • PostgreSQL 8.4 及更高版本:
    https://www.postgresql.org/docs/current/static/queries-with.html
  • Sybase 11 及更高版本:
    http://dcx.sybase.com/1100/en/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
  • Informix 14.10 及更高版本:https://www.ibm.com/support/knowledgecenter/SSGU8G_14.1.0/com.ibm.sqls.doc/ids_sqs_with.htm

相关文章