MySQL:在 FROM 子句限制中使用子查询查看

2021-11-20 00:00:00 sql mysql view mysql-error-1349

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

ERROR 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

相关文章