MySQL Mysql子查询导致“where”子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8590421/
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 subquery result in "where" clause
提问by Eddie
Is it possible to execute mysql query like this?
是否可以像这样执行 mysql 查询？
select (select A from B where ... ) as C from D where C like ' ... '
I need to use the result of subquery in general "where" clause.
我需要在一般“where”子句中使用子查询的结果。
回答by a'r
You can wrap it in a sub-query like this:
您可以将其包装在子查询中，如下所示：
SELECT *
FROM (
select (select A from B where ... ) as C from D
) subq
WHERE
C like ' ... '
回答by Derk Arts
Have you read this?
你读过这个吗？
http://dev.mysql.com/doc/refman/5.0/en/subqueries.html
http://dev.mysql.com/doc/refman/5.0/en/subqueries.html
A subquery is a SELECT statement within another statement.
Starting with MySQL 4.1, all subquery forms and operations that the SQL standard requires are supported, as well as a few features that are MySQL-specific.
Here is an example of a subquery:
子查询是另一个语句中的 SELECT 语句。
从 MySQL 4.1 开始，支持 SQL 标准要求的所有子查询形式和操作，以及一些特定于 MySQL 的功能。
下面是一个子查询的例子：
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
In this example, SELECT * FROM t1 ... is the outer query (or outer statement), and (SELECT column1 FROM t2) is the subquery. We say that the subquery is nested within the outer query, and in fact it is possible to nest subqueries within other subqueries, to a considerable depth. A subquery must always appear within parentheses.
The main advantages of subqueries are:
They allow queries that are structured so that it is possible to isolate each part of a statement.
They provide alternative ways to perform operations that would otherwise require complex joins and unions.
Many people find subqueries more readable than complex joins or unions. Indeed, it was the innovation of subqueries that gave people the original idea of calling the early SQL “Structured Query Language.”
Here is an example statement that shows the major points about subquery syntax as specified by the SQL standard and supported in MySQL:
在此示例中，SELECT * FROM t1 ... 是外部查询（或外部语句），而 (SELECT column1 FROM t2) 是子查询。我们说子查询嵌套在外部查询中，实际上可以将子查询嵌套在其他子查询中，达到相当的深度。子查询必须始终出现在括号内。
子查询的主要优点是：
它们允许结构化的查询，以便可以隔离语句的每个部分。
它们提供了执行操作的替代方法，否则这些操作将需要复杂的连接和联合。
许多人发现子查询比复杂的连接或联合更具可读性。的确，正是子查询的创新，让人们有了将早期 SQL 称为“结构化查询语言”的最初想法。
这是一个示例语句，显示了 SQL 标准指定并在 MySQL 中支持的子查询语法的要点：
回答by Aaron
While this is actually valid SQL:
虽然这实际上是有效的 SQL：
select (select A from B where A = D.A ) as C
from D
You are much better-off (performance-wise) implementing a JOIN instead:
改为实施 JOIN 会更好（性能方面）：
SELECT D.A
FROM D
INNER JOIN B ON B.A = D.A
回答by xQbert
No not as illustrated; but you could run the query in both places. or create a temp table with those results and join them in making it available to the query..
不，如图所示；但您可以在两个地方运行查询。或使用这些结果创建一个临时表并加入它们以使其可用于查询..
Select C from D inner join (Select A from B where...) C on C.1=D.1 where C like....
Select C from D inner join (Select A from B where...) C on C.1=D.1 where C like....
回答by Lee Louviere
If you mean comparing the results of a subquery in a where, Yes you can do this.
如果您的意思是在 where 中比较子查询的结果，是的，您可以这样做。
select X ... where (select Y ... ) = Z
select X ... where (select Y ... ) = Z
However, probably a bad idea. Generally when you have to do this, it's best to look for a way to streamline it into the main select. There are very creative ways to avoid nested queries. The reason being is that every selection has to do the inner query every time
然而，这可能是一个坏主意。通常，当您必须这样做时，最好寻找一种方法将其简化为主选择。有一些非常有创意的方法可以避免嵌套查询。原因是每次选择都必须每次都进行内部查询
If you mean performing a where against a subquery, you just select all and apply where. However, again, you could just apply another where.
如果您的意思是对子查询执行 where，您只需全选并应用 where。但是，同样，您可以应用另一个地方。
select X ... where Y = Z and A = B.
select X ... where Y = Z and A = B.
回答by Prabhu Nandan Kumar
This is always better to join table instead of subquery. i.e. Subquery :
这总是更好地连接表而不是子查询。即子查询：
SELECT id, (SELECT abc FROM t2 WHERE ID = user_id) as user_name FROM t1
Join :
加入 ：
SELECT c.id,u.abc FROM t1 as c LEFT JOIN t2 AS u ON u.ID = c.user_id WHERE 1=1 AND u.`user_login` = 'qwe123'
If we try to put where clause in subquery, it throw error.
如果我们尝试在子查询中放置 where 子句，它会抛出错误。
