SQL select中子查询语句的where子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13261071/
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
Where clause on subquery statement in select
提问by user194076
Let's say I have a query like this:
假设我有一个这样的查询：
Select col1,
col2,
(select count(smthng) from table2) as 'records'
from table1
I want to filter it to be not null for 'records' column.
我想将它过滤为“记录”列不为空。
I cannot do this:
我不能做到这一点：
Select col1,
col2,
(select count(smthng) from table2) as 'records'
from table1
where records is not null
The best I came up with is to write this resultset to a Table Value parameter and have a separate query on that resultset. Any ideas?
我想出的最好方法是将此结果集写入表值参数，并对该结果集进行单独查询。有任何想法吗？
回答by RichardTheKiwi
Just move it to a derived query. You cannot use a column defined in the SELECT clause in the WHERE clause.
只需将其移动到派生查询即可。您不能在 WHERE 子句中使用在 SELECT 子句中定义的列。
Select col1, col2, records
from
(
Select col1,
col2,
(select ..... from table2) as records
from table1
) X
where records is not null;
回答by Rikki
You should do some little modifications there:
你应该在那里做一些小的修改：
First of all, add TOP clause on the subquery to force the query to return only one record of that table2. A subquery like this one you have should return only an scalar value.
首先，在子查询上添加TOP子句，强制查询只返回该表2的一条记录。像这样的子查询应该只返回一个标量值。
Secondly, the subquery can have only one column in its column list, so again the return value should be a scalar one.
其次，子查询在其列列表中只能有一个列，因此返回值应该是一个标量。
At last, you cannot filter the subquery or any made column in a select clause. So my recommendation is to either use "join"s or "exists".
最后，您不能在 select 子句中过滤子查询或任何生成的列。所以我的建议是使用"join"s 或"exists".
Select col1,
col2
from table1
left outer join
table2
on table1.key = table2.key
where not table2.key is null
Or this:
或这个：
Select col1,
col2
from table1
inner join
table2
on table1.key = table2.key
Or this one:
或者这个：
Select col1,
col2
from table1
where exists (
select *
from table2
where table2.key = table1.key
and not table2.somethingelse is null
-- or any other relevant conditions
)
Cheers
干杯
