MySQL:FROM子句限制中的带有子查询的视图

本文介绍了MySQL:FROM子句限制中的带有子查询的视图的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

在MySQL 5.0中，尝试在FROM子句中创建带有子查询的视图时，为什么会发生以下错误?

In MySQL 5.0 why does the following error occur when trying to create a view with a subquery in the FROM clause?

错误1349(HY000):视图的SELECT在FROM子句中包含一个子查询

ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause

如果这是MySQL引擎的限制，那么为什么他们还没有实现此功能?

If this is a limitation of the MySQL engine, then why haven't they implemented this feature yet?

此外，此限制有哪些好的解决方法?

Also, what are some good workarounds for this limitation?

在FROM子句中是否有任何变通方法适用于任何子查询?或者是否存在一些在FROM子句中不使用子查询就无法表达的查询?

Are there any workarounds that work for any subquery in the FROM clause or are there some queries that can not be expressed without using a subquery in the FROM clause?

一个示例查询(包含在注释中):

An example query (was buried in a comment):

SELECT temp.UserName FROM (SELECT u1.name as UserName, COUNT(m1.UserFromId) as SentCount FROM Message m1, User u1 WHERE u1.uid = m1.UserFromId Group BY u1.name HAVING SentCount > 3 ) as temp

推荐答案

您的查询不能仅写为:

SELECT u1.name as UserName from Message m1, User u1 WHERE u1.uid = m1.UserFromID GROUP BY u1.name HAVING count(m1.UserFromId)>3

这也应该有助于解决MySQL子查询中的已知速度问题

That should also help with the known speed issues with subqueries in MySQL