MySQL的“WITH”子句

Question

MySQL的“WITH”子句

116

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

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

但似乎MySQL不支持这个功能。

我认为这是相当标准的，Oracle肯定支持这个功能。有没有办法强制MySQL使用"WITH"子句？我尝试过MyISAM和InnoDB引擎，但都不行。

- Bill Collins

8个回答

23

你可能会对这个感兴趣：

select * from (
    select * from table
) as Subquery

- Mosty Mostacho

请问您能解释一下子查询吗？我可以这样写吗？ select * from ( (select * from table1) UNION ALL (select * from table2) ) Group By something? - user677607

1

@Kathy 你好，Subquery 是我用来表示派生表本身的名称。当你使用 from ( ... ) 时，你创建了类似于临时表（派生表），它需要一个名称。这就是为什么我使用了 as Subquery。回答你的问题，是的，你可以这样做，但你必须给外部派生表命名（就在 Group By 之前）。希望这有所帮助。 - Mosty Mostacho

@MostyMostacho 你好，能否给我一点提示？我正在努力将它转换为MySQL。你能看一下吗？链接或者在这里回答我的问题也可以吗？链接 - Pranav

14

您已经掌握了正确的语法：

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

然而，正如其他人所提到的，MySQL不支持这个命令。WITH是在SQL:1999中添加的；最新版本的SQL标准是SQL:2008。您可以在维基百科上找到一些关于支持SQL:1999各种功能的数据库的更多信息。

与商业数据库如Oracle、SQL Server（最近）和DB2相比，MySQL在支持SQL标准方面传统上落后了一点。PostgreSQL通常也很符合标准。

您可能需要查看MySQL的路线图；我不完全确定何时会支持此功能，但它非常适合创建可读的汇总查询。

- Ed Altorfer

10

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 的回答，但我正在清理 ysth 可能引起的任何混淆。

- Mark Brady

无论如何都没有解决我的困惑。您的意思是说没有WITH子句但有WITH语句吗？ - ysth

1

啊，我明白了。它是在select之前的一个子句。它可以在CREATE VIEW中使用吗？它与连接子查询有什么不同？我在网上没有看到名称后面带有参数的示例 - 它们是如何工作的？ - ysth

1

非常不同。请注意，相同的子查询被使用两次而无需定义两次。当然，您可以复制/粘贴相同的查询，但这只是一个简单的例子。想象一下如果WITH子句延续了一页，并在主查询中使用了4次，那么您将会感激它。 - Mark Brady

我已经链接到了文档，那里应该会解释语法。就视图而言，在那里肯定是有效的。 - Mark Brady

6

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

您可能需要使用带有宏功能的文本编辑器将值列表转换为带引号的选择联合子句。

- Reuben

2

MariaDB 现在支持 WITH，而 MySQL 目前不支持。 https://mariadb.com/kb/en/mariadb/with/

- Moshe L

2

   WITH authorRating as (select aname, rating from book)
   SELECT aname, AVG(quantity)
   FROM authorRating
   GROUP BY aname

- Mantas Dainys

1

你是否尝试过临时表？这解决了我的问题：

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

1

我必须注意：https://dev59.com/vHRC5IYBdhLWcg3wS_EF 你不能两次打开同一张表 :-( - Claus

我针对小数据集的表格提供的解决方案是：创建一个名为abc2的表格，其结构与abc相同；然后将abc中的所有数据插入到abc2中。 - Claus

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Bill Karwin · Accepted Answer

更新：MySQL 8.0终于获得了使用通用表达式（包括递归CTEs）的功能。

这是一篇宣布此消息的博客：http://mysqlserverteam.com/mysql-8-0-labs-recursive-common-table-expressions-in-mysql-ctes/

以下是我在2008年撰写的答案：

MySQL 5.x不支持使用SQL-99中定义的WITH语法或称为“通用表达式”进行查询。

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

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

Oracle 9i release 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及更高版本：
PostgreSQL: 支持公共表表达式（CTE），允许在SELECT、INSERT、UPDATE、DELETE语句中使用WITH子句定义临时表。
Sybase 11及以后版本：支持公共表表达式，可用于查询和视图的定义。
SQLite 3.8.3及以后版本：支持公共表表达式，但不支持递归公共表表达式。
HSQLDB：支持公共表表达式，并提供与SQL:1999标准兼容的可重用表。
Firebird 2.1及之后版本（第一个支持递归查询的开源数据库管理系统）：支持递归公共表表达式。
H2 Database：仅支持递归公共表表达式。
Informix 14.10及以后版本：支持公共表表达式，包括递归公共表表达式。