未使用EXISTS引入子查询时，只能在选择列表中指定一个表达式 [英] Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

问题描述

我的查询如下，并且其中包含一个子查询:

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?

推荐答案

您不能在子查询中返回两个(或多个)列以在WHERE A_ID IN (subquery)子句中进行比较-应该比较哪个列A_ID到?您的子查询必须仅将进行比较所需的一列返回到IN另一侧的列.因此查询的形式必须为:

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:

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

您还想添加排序，以便只从顶部几行中进行选择，但是您无需返回COUNT作为一列即可进行排序； ORDER子句中的排序与查询返回的列无关.

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.

尝试这样的事情:

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)

这篇关于未使用EXISTS引入子查询时，只能在选择列表中指定一个表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！