MySQL "WITH" clause

 

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

But it doesn't seem like MySQL supports this.

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.

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/

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

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

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

Other RDBMS products that support common table expressions:

  • 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

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

  • 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

    • 你可能会对这样的事情感兴趣： 

    select * from (
    select * from table
) as Subquery

    You've got the syntax right: 

    WITH AuthorRating(AuthorName, AuthorRating) AS
   SELECT aname         AS AuthorName,
          AVG(quantity) AS AuthorRating
   FROM Book
   GROUP By Book.aname

    However, as others have mentioned, MySQL does not support this command. WITH was added in SQL:1999; the newest version of the SQL standard is SQL:2008. You can find some more information about databases that support SQL:1999's various features on Wikipedia.

    MySQL has traditionally lagged a bit in support for the SQL standard, whereas commercial databases like Oracle, SQL Server (recently), and DB2 have followed them a bit more closely. PostgreSQL is typically pretty standards compliant as well.

    You may want to look at MySQL's roadmap; I'm not completely sure when this feature might be supported, but it's great for creating readable roll-up queries.

    链接地址: http://www.djcxy.com/p/30458.html

    上一篇: 如何从平面结构高效地构建树？

    下一篇: MySQL的“WITH”子句