MySQL“WITH”子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/324935/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL "WITH" clause
提问by Bill Karwin
I'm trying to use MySQL to create a view with the "WITH" clause
我正在尝试使用 MySQL 创建带有“WITH”子句的视图
WITH authorRating(aname, rating) AS
SELECT aname, AVG(quantity)
FROM book
GROUP BY aname
But it doesn't seem like MySQL supports this.
但似乎 MySQL 不支持这一点。
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.
我认为这是非常标准的，我确信 Oracle 支持这一点。无论如何强制MySQL使用“WITH”子句？我已经用 MyISAM 和 innoDB 引擎试过了。这两个都不起作用。
回答by Bill Karwin
Update: MySQL 8.0 is finally getting the feature of common table expressions, including recursive CTEs.
更新：MySQL 8.0 终于获得了公共表表达式的特性，包括递归 CTE。
Here's a blog announcing it: http://mysqlserverteam.com/mysql-8-0-labs-recursive-common-table-expressions-in-mysql-ctes/
这是一个宣布它的博客：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.
以下是我之前的回答，最初是在 2008 年写的。
MySQL 5.x does not support queries using the WITHsyntax defined in SQL-99, also called Common Table Expressions.
MySQL 5.x 不支持使用WITHSQL-99 中定义的语法的查询，也称为公共表表达式。
This has been a feature request for MySQL since January 2006: http://bugs.mysql.com/bug.php?id=16244
自 2006 年 1 月以来，这是 MySQL 的功能请求：http: //bugs.mysql.com/bug.php?id=16244
Other RDBMS products that support common table expressions:
其他支持公用表表达式的 RDBMS 产品：
- 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 onlyrecursive):
http://www.h2database.com/html/advanced.html#recursive_queries
- 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
Other databases that lack support for the WITH clause (as of February 2014):
其他不支持 WITH 子句的数据库（截至 2014 年 2 月）：
- Informix (though Informix does support the
CONNECT BYsyntax 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
- Informix（尽管 Informix 确实支持
CONNECT BYOracle 曾经使用的语法）
http://pic.dhe.ibm.com/infocenter/idshelp/v115/index.jsp?topic=%2Fcom.ibm.sqls.doc%2Fids_sqs_2033。 htm
回答by Mosty Mostacho
You might be interested in somethinkg like this:
你可能对这样的想法感兴趣：
select * from (
select * from table
) as Subquery
回答by Ed Altorfer
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 不支持此命令。SQL:1999 中添加了 WITH；SQL 标准的最新版本是 SQL:2008。您可以在Wikipedia上找到有关支持 SQL:1999 各种功能的数据库的更多信息。
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.
MySQL 传统上在对 SQL 标准的支持方面稍有滞后，而像 Oracle、SQL Server（最近）和 DB2 等商业数据库则更加紧随其后。PostgreSQL 通常也非常符合标准。
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.
您可能想查看 MySQL 的路线图；我不完全确定何时可能支持此功能，但它非常适合创建可读的汇总查询。
回答by Ed Altorfer
Oracle does support WITH.
Oracle 确实支持 WITH。
It would look like this.
它看起来像这样。
WITH emps as (SELECT * FROM Employees)
SELECT * FROM emps WHERE ID < 20
UNION ALL
SELECT * FROM emps where Sex = 'F'
@ysth WITH is hard to google because it's a common word typically excluded from searches.
@ysth WITH 很难用谷歌搜索，因为它是一个通常被排除在搜索之外的常用词。
You'd want to look at the SELECT docsto see how subquery factoring works.
您需要查看SELECT 文档以了解子查询分解的工作原理。
I know this doesn't answer the OP but I'm cleaning up any confusion ysth may have started.
我知道这不能回答 OP，但我正在清理 ysth 可能已经开始的任何混乱。
回答by Moshe L
MariaDB is now supporting WITH. MySQL for now is not. https://mariadb.com/kb/en/mariadb/with/
MariaDB 现在支持 WITH。MySQL 目前不是。 https://mariadb.com/kb/en/mariadb/with/
回答by Reuben
Building on the answer from @Mosty Mostacho, here's how you might do something equivalent in MySQL,for a specific case of determining what entries don't exist in a table, and are not in any other database.
基于@Mosty Mostacho 的回答，以下是您如何在 MySQL 中执行等效操作，用于确定表中不存在哪些条目并且不在任何其他数据库中的特定情况。
select col1 from (
select 'value1' as col1 union
select 'value2' as col1 union
select 'value3' as col1
) as subquery
left join mytable as mytable.mycol = col1
where mytable.mycol is null
order by col1
You may want to use a text editor with macro capabilities to convert a list of values to the quoted select union clause.
您可能希望使用具有宏功能的文本编辑器将值列表转换为带引号的 select union 子句。
回答by Claus
Have you ever tried Temporary Table? This solved my convern:
你有没有试过临时表？这解决了我的问题：
create temporary table abc (
column1 varchar(255)
column2 decimal
);
insert into abc
select ...
or otherwise
insert into abc
values ('text', 5.5), ('text2', 0815.8);
Then you can use this table in every select in this session:
然后您可以在此会话的每个选择中使用此表：
select * from abc inner join users on ...;
回答by Mantas Dainys
WITH authorRating as (select aname, rating from book)
SELECT aname, AVG(quantity)
FROM authorRating
GROUP BY aname
