SQL 当子查询没有用EXISTS引入时，select列表中只能指定一个表达式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1904314/
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
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS
提问by rockit
My query is as follows, and contains a subquery within it:
我的查询如下，其中包含一个子查询：
select count(distinct dNum)
from myDB.dbo.AQ
where A_ID in
(SELECT DISTINCT TOP (0.1) PERCENT A_ID,
COUNT(DISTINCT dNum) AS ud
FROM myDB.dbo.AQ
WHERE M > 1 and B = 0
GROUP BY A_ID ORDER BY ud DESC)
The error I am receiving is ...
我收到的错误是...
Only one expression can be specified in the select list when the subquery is not
introduced with EXISTS.`
When I run the sub-query alone, it returns just fine, so I am assuming there is some issue with the main query?
当我单独运行子查询时，它返回得很好，所以我假设主查询存在一些问题？
回答by Chris Latta
You can't return two (or multiple) columns in your subquery to do the comparison in the WHERE A_ID IN (subquery)clause - which column is it supposed to compare A_IDto? Your subquery must only return the one column needed for the comparison to the column on the other side of the IN. So the query needs to be of the form:
您不能在子查询中返回两个（或多个）列来在WHERE A_ID IN (subquery)子句中进行比较- 它应该与哪一列进行比较A_ID？您的子查询必须只返回与IN. 因此，查询需要采用以下形式：
SELECT * From ThisTable WHERE ThisColumn IN (SELECT ThatColumn FROM ThatTable)
You also want to add sorting so you can select just from the top rows, but you don't need to return the COUNT as a column in order to do your sort; sorting in the ORDERclause is independent of the columns returned by the query.
您还想添加排序，以便您可以仅从顶部行中进行选择，但您不需要将 COUNT 作为列返回来进行排序；ORDER子句中的排序与查询返回的列无关。
Try something like this:
尝试这样的事情：
select count(distinct dNum)
from myDB.dbo.AQ
where A_ID in
(SELECT DISTINCT TOP (0.1) PERCENT A_ID
FROM myDB.dbo.AQ
WHERE M > 1 and B = 0
GROUP BY A_ID
ORDER BY COUNT(DISTINCT dNum) DESC)
回答by ShoushouLeb
You should return only one column and one row in the where query where you assign the returned value to a variable. Example:
您应该在 where 查询中只返回一列和一行，您可以在其中将返回值分配给变量。例子：
select * from table1 where Date in (select * from Dates) -- Wrong
select * from table1 where Date in (select Column1,Column2 from Dates) -- Wrong
select * from table1 where Date in (select Column1 from Dates) -- OK
回答by Jim L
It's complaining about
它在抱怨
COUNT(DISTINCT dNum) AS ud
inside the subquery. Only one column can be returned from the subquery unless you are performing an exists query. I'm not sure why you want to do a count on the same column twice, superficially it looks redundant to what you are doing. The subquery here is only a filterit is not the same as a join. i.e. you use it to restrict data, not to specify what columns to get back.
在子查询里面。除非您正在执行一个存在的查询，否则子查询只能返回一列。我不确定你为什么要对同一列进行两次计数，从表面上看，这对你正在做的事情来说是多余的。这里的子查询只是一个过滤器，它与连接不同。即您使用它来限制数据，而不是指定要返回的列。
回答by SVK
Apart from very good responses here, you could try this as well if you want to use your sub query as is.
除了这里非常好的响应之外，如果您想按原样使用子查询，您也可以尝试这样做。
Approach:
方法：
1) Select the desired column (Only 1) from your sub query
1) 从您的子查询中选择所需的列（仅 1）
2) Use where to map the column name
2）使用where来映射列名
Code:
代码：
SELECT count(distinct dNum)
FROM myDB.dbo.AQ
WHERE A_ID in
(
SELECT A_ID
FROM (SELECT DISTINCT TOP (0.1) PERCENT A_ID, COUNT(DISTINCT dNum) AS ud
FROM myDB.dbo.AQ
WHERE M > 1 and B = 0
GROUP BY A_ID ORDER BY ud DESC
) a
)
