当子查询没有用EXISTS引入时，select列表中只能指定一个表达式

2021-12-02 00:00:00 sql tsql sql-server 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)

相关文章