mysql with select,sql - MySQL“WITH”子句-优快云博客

sql - MySQL“WITH”子句

我正在尝试使用MySQL创建一个带有“WITH”子句的视图

WITH authorRating(aname, rating) AS

SELECT aname, AVG(quantity)

FROM book

GROUP BY aname

但似乎MySQL并不支持这一点。

我认为这是非常标准的，我确信Oracle支持这一点。反正强制MySQL使用“WITH”子句吗？我用MyISAM和innoDB引擎试过了。这两个都不起作用。

7个解决方案

87 votes

更新：MySQL 8.0最终获得了常用表表达式的功能，包括递归CTE。

这是一个宣布它的博客：[http://mysqlserverteam.com/mysql-8-0-labs-recursive-common-table-expressions-in-mysql-ctes/]

以下是我之前在2008年写的答案。

MySQL不支持使用SQL-99中定义的WITH语法进行查询，也称为公用表表达式。

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

其他支持公用表表达式的RDBMS产品：

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]

其他缺乏对WITH子句支持的数据库(截至2014年2月)：

Informix(虽然Informix确实支持曾经被Oracle使用的CONNECT BY语法)

[http://pic.dhe.ibm.com/infocenter/idshelp/v115/index.jsp?topic=%2Fcom.ibm.sqls.doc%2Fids_sqs_2033.htm]

Bill Karwin answered 2019-04-27T14:10:35Z

13 votes

您可能对以下内容感兴趣：

select * from (

select * from table

) as Subquery

Mosty Mostacho answered 2019-04-27T14:11:02Z

11 votes

你有正确的语法：

WITH AuthorRating(AuthorName, AuthorRating) AS

SELECT aname AS AuthorName,

AVG(quantity) AS AuthorRating

FROM Book

GROUP By Book.aname

但是，正如其他人所提到的，MySQL不支持此命令。在SQL中添加了WITH：1999; 最新版本的SQL标准是SQL：2008。您可以找到有关支持SQL的数据库的更多信息：1999年维基百科上的各种功能。

传统上，MySQL在支持SQL标准方面略有滞后，而Oracle，SQL Server(最近)和DB2等商业数据库则更加密切关注它们。 PostgreSQL通常也符合标准。

你可能想看看MySQL的路线图; 我不完全确定何时可以支持此功能，但它非常适合创建可读的汇总查询。

Ed Altorfer answered 2019-04-27T14:11:52Z

6 votes

Oracle确实支持WITH。

它看起来像这样。

WITH emps as (SELECT * FROM Employees)

SELECT * FROM emps WHERE ID < 20

UNION ALL

SELECT * FROM emps where Sex = 'F'

@ysth WITH很难谷歌，因为它是一个通常被排除在搜索之外的词。

您需要查看SELECT文档以了解子查询因子如何工作。

我知道这不会回答OP，但我正在清理任何可能已经开始的混乱。

answered 2019-04-27T14:12:49Z

2 votes

在@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

您可能希望使用具有宏功能的文本编辑器将值列表转换为带引号的select union子句。

Reuben answered 2019-04-27T14:13:24Z

1 votes

你有没有试过临时表？这解决了我的担忧：

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);

然后，您可以在此会话中的每个选择中使用此表：

select * from abc inner join users on ...;

Claus answered 2019-04-27T14:13:56Z

0 votes

MariaDB现在支持WITH。 MySQL现在不是。[https://mariadb.com/kb/en/mariadb/with/]

Moshe L answered 2019-04-27T14:14:25Z