MySQL：在 FROM 子句限制中使用子查询查看
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/206062/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL: View with Subquery in the FROM Clause Limitation
提问by Daniel
In MySQL 5.0 why does the following error occur when trying to create a view with a subquery in the FROM clause?
在 MySQL 5.0 中，为什么尝试在 FROM 子句中创建带有子查询的视图时会出现以下错误？
ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause
错误 1349 (HY000)：视图的 SELECT 在 FROM 子句中包含子查询
If this is a limitation of the MySQL engine, then why haven't they implemented this feature yet?
如果这是 MySQL 引擎的限制，那为什么他们还没有实现这个功能呢？
Also, what are some good workarounds for this limitation?
另外，针对此限制有哪些好的解决方法？
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?
是否有任何解决方法适用于 FROM 子句中的任何子查询，或者是否有一些查询在不使用 FROM 子句中的子查询的情况下无法表达？
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
采纳答案by Grant Limberg
Couldn't your query just be written as:
你的查询不能写成：
SELECT u1.name as UserName from Message m1, User u1
WHERE u1.uid = m1.UserFromID GROUP BY u1.name HAVING count(m1.UserFromId)>3
That should also help with the known speed issues with subqueries in MySQL
这也应该有助于解决 MySQL 中子查询的已知速度问题
回答by Json on Linux Apache MySQL
I had the same problem. I wanted to create a view to show information of the most recent year, from a table with records from 2009 to 2011. Here's the original query:
我有同样的问题。我想创建一个视图来显示最近一年的信息，从一个包含 2009 年到 2011 年记录的表。这是原始查询：
SELECT a.*
FROM a
JOIN (
SELECT a.alias, MAX(a.year) as max_year
FROM a
GROUP BY a.alias
) b
ON a.alias=b.alias and a.year=b.max_year
Outline of solution:
解决方案概要：
- create a view for each subquery
- replace subqueries with those views
- 为每个子查询创建一个视图
- 用这些视图替换子查询
Here's the solution query:
这是解决方案查询：
CREATE VIEW v_max_year AS
SELECT alias, MAX(year) as max_year
FROM a
GROUP BY a.alias;
CREATE VIEW v_latest_info AS
SELECT a.*
FROM a
JOIN v_max_year b
ON a.alias=b.alias and a.year=b.max_year;
It works fine on mysql 5.0.45, without much of a speed penalty (compared to executing the original sub-query select without any views).
它在 mysql 5.0.45 上运行良好，没有太大的速度损失（与在没有任何视图的情况下执行原始子查询选择相比）。
回答by Nikki9696
It appears to be a known issue.
这似乎是一个已知问题。
http://dev.mysql.com/doc/refman/5.1/en/unnamed-views.html
http://dev.mysql.com/doc/refman/5.1/en/unnamed-views.html
http://bugs.mysql.com/bug.php?id=16757
http://bugs.mysql.com/bug.php?id=16757
Many IN queries can be re-written as (left outer) joins and an IS (NOT) NULL of some sort. for example
许多 IN 查询可以重写为（左外）连接和某种形式的 IS (NOT) NULL。例如
SELECT * FROM FOO WHERE ID IN (SELECT ID FROM FOO2)
can be re-written as
可以重写为
SELECT FOO.* FROM FOO JOIN FOO2 ON FOO.ID=FOO2.ID
or
或者
SELECT * FROM FOO WHERE ID NOT IN (SELECT ID FROM FOO2)
can be
可
SELECT FOO.* FROM FOO
LEFT OUTER JOIN FOO2
ON FOO.ID=FOO2.ID WHERE FOO.ID IS NULL
回答by Dexin Wang
create a view for each subquery is the way to go. Got it working like a charm.
为每个子查询创建一个视图是要走的路。让它像魅力一样工作。
回答by Gruff
You can work around this by creating a separate VIEW for any subquery you want to use and then join to that in the VIEW you're creating. Here's an example: http://blog.gruffdavies.com/2015/01/25/a-neat-mysql-hack-to-create-a-view-with-subquery-in-the-from-clause/
您可以通过为要使用的任何子查询创建单独的 VIEW 然后加入您正在创建的 VIEW 来解决此问题。这是一个例子：http: //blog.gruffdavies.com/2015/01/25/a-neat-mysql-hack-to-create-a-view-with-subquery-in-the-from-clause/
This is quite handy as you'll very likely want to reuse it anyway and helps you keep your SQL DRY.
这非常方便，因为您很可能无论如何都想重用它并帮助您保持 SQL DRY。
