在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子句限制中的带有子查询的视图
发布评论