不使用 EXISTS 引入子查询时,选择列表中只能指定一个表达式

本教程将介绍不使用 EXISTS 引入子查询时,选择列表中只能指定一个表达式的处理方法,这篇教程是从别的地方看到的,然后加了一些国外程序员的疑问与解答,希望能对你有所帮助,好了,下面开始学习吧。

不使用 EXISTS 引入子查询时,选择列表中只能指定一个表达式 教程 第1张

问题描述

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 
 FROMmyDB.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 引入子查询时,选择列表中只能指定一个表达式的教程就到这里就结束了,希望趣模板源码网找到的这篇技术文章能帮助到大家,更多技术教程可以在站内搜索。