代码错误.如果没有使用EXISTS引入子查询，则只能在选择列表中指定一个表达式

Question

我正在尝试运行这个查询，但无论我做什么，我都会不断收到这个错误，我无法解决它。如有任何帮助，我们将不胜感激。

select
CC.ContactID, 
from 
Client C 
join Contacts CC on CC.ContactID = C.ContactID 
where 
ClientID In (

Select 
ClientID,Sum(Total-allocated) as Bal 
from 
Main 
Where 
Total <> Allocated
and NomTypeID < 10 
Group By 
ClientID
HAVING Sum(Total-allocated) > 10
)

Answer 1

我不确定你使用的是什么类型的DB(所以我也无法测试我的答案(。

但通常，当您使用IN时，您需要子查询只返回一列。

因此，查询应该是(删除Sum(Total-allocated) as Bal(：

select
CC.ContactID, 
from 
Client C 
join Contacts CC on CC.ContactID = C.ContactID 
where 
ClientID In (

Select 
ClientID 
from 
Main 
Where 
Total <> Allocated
and NomTypeID < 10 
Group By 
ClientID
HAVING Sum(Total-allocated) > 10
)

Answer 2

请使用以下查询，

select
CC.ContactID from  
Client C 
join Contacts CC on CC.ContactID = C.ContactID 
join (Select ClientID,Sum(Total-allocated) as Bal from Main Where 
Total <> Allocated and NomTypeID < 10 
Group By ClientID HAVING Sum(Total-allocated) > 10)) m
on (C.ClientID = m.ClientID);