MySQL:FROM子句限制中的带有子查询的视图 [英] MySQL: View with Subquery in the FROM Clause Limitation

问题描述

在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

这篇关于MySQL:FROM子句限制中的带有子查询的视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！