不使用 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?

解决方案

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)

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.

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)

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