SQL Server 中的嵌套选择语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4629979/
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
Nested select statement in SQL Server
提问by Brennan Vincent
Why doesn't the following work?
为什么以下不起作用？
SELECT name FROM (SELECT name FROM agentinformation)
I guess my understanding of SQL is wrong, because I would have thought this would return the same thing as
我想我对 SQL 的理解是错误的，因为我原以为这会返回与
SELECT name FROM agentinformation
Doesn't the inner select statement create a result set which the outer SELECT statement then queries?
内部 select 语句不是创建一个结果集，然后外部 SELECT 语句会查询吗？
回答by Joe Stefanelli
You need to alias the subquery.
您需要为子查询添加别名。
SELECT name FROM (SELECT name FROM agentinformation) a
or to be more explicit
或者更明确
SELECT a.name FROM (SELECT name FROM agentinformation) a
回答by Somnath Muluk
The answerprovided by Joe Stefanelli is already correct.
Joe Stefanelli 提供的答案已经是正确的。
SELECT name FROM (SELECT name FROM agentinformation) as a
We need to make an alias of the subquery because a query needs a table object which we will get from making an alias for the subquery. Conceptually, the subquery results are substituted into the outer query. As we need a table object in the outer query, we need to make an alias of the inner query.
我们需要为子查询创建别名，因为查询需要一个表对象，我们将从为子查询创建别名中获得该对象。从概念上讲，子查询结果被替换到外部查询中。由于我们在外部查询中需要一个表对象，我们需要为内部查询创建一个别名。
Statements that include a subquery usually take one of these forms:
包含子查询的语句通常采用以下形式之一：
- WHERE expression [NOT] IN (subquery)
- WHERE expression comparison_operator [ANY | ALL] (subquery)
- WHERE [NOT] EXISTS (subquery)
- WHERE 表达式 [NOT] IN（子查询）
- WHERE 表达式 compare_operator [ANY | ALL]（子查询）
- WHERE [NOT] EXISTS（子查询）
Check for more subquery rulesand subquery types.
More examplesof Nested Subqueries.
嵌套子查询的更多示例。
IN / NOT IN – This operator takes the output of the inner query after the inner query gets executed which can be zero or more values and sends it to the outer query. The outer query then fetches all the matching [IN operator] or non matching [NOT IN operator] rows.
ANY – [>ANY or ANY operator takes the list of values produced by the inner query and fetches all the values which are greater than the minimum value of the list. The
IN / NOT IN – 此运算符在内部查询执行后获取内部查询的输出，该输出可以是零个或多个值，并将其发送到外部查询。然后外部查询获取所有匹配的 [IN operator] 或不匹配的 [NOT IN operator] 行。
ANY – [>ANY 或 ANY 运算符获取由内部查询生成的值列表，并获取大于列表最小值的所有值。这
e.g. >ANY(100,200,300), the ANY operator will fetch all the values greater than 100.
例如 >ANY(100,200,300)，ANY 运算符将获取所有大于 100 的值。
- ALL – [>ALL or ALL operator takes the list of values produced by the inner query and fetches all the values which are greater than the maximum of the list. The
- ALL – [>ALL 或 ALL 运算符获取由内部查询生成的值列表，并获取大于列表最大值的所有值。这
e.g. >ALL(100,200,300), the ALL operator will fetch all the values greater than 300.
例如 >ALL(100,200,300)，ALL 运算符将获取所有大于 300 的值。
- EXISTS – The EXISTS keyword produces a Boolean value [TRUE/FALSE]. This EXISTS checks the existence of the rows returned by the sub query.
- EXISTS – EXISTS 关键字产生一个布尔值 [TRUE/FALSE]。此 EXISTS 检查子查询返回的行是否存在。
