当不存在子查询时，在select列表中只能指定一个表达式

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?

当我单独运行子查询时，它返回的结果很好，所以我假设主查询有问题?

4 个解决方案

#1

167

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_ID to? 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(子查询)子句中进行比较——应该将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 ORDER clause is independent of the columns returned by the query.

你还需要添加排序，这样你就可以从最上面的行中进行选择，但是你不需要将计数作为一列返回来进行排序;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)

#2

You should return only one column and one row in the where query where you assign the returned value to a variable. Example:

您应该只返回where查询中的一列和一行，在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

#3

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 filter it is not the same as a join. i.e. you use it to restrict data, not to specify what columns to get back.

内部子查询。只有一列可以从子查询返回，除非您正在执行一个现有查询。我不知道为什么你要在同一列上重复计算两次，表面上看你做的是多余的。这里的子查询只是一个过滤器，它与连接不一样。例如，您使用它来限制数据，而不是指定要返回哪些列。

#4

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 
   )

#1

167

SELECT * From ThisTable WHERE ThisColumn IN (SELECT ThatColumn FROM ThatTable)