错误sql oracle "ORA-00936:表达式缺失“

Question

我的SQL缺少什么？

select cl.nom_cl
from client cl
where (cl.id_client IN(select e.id_acheteur
    from enchere e 
    group by e.id_acheteur 
    having(  count(distinct e.idobj) = select count(distinct e2.idobj) from enchere e2 )))

ORA-00936:缺席的表达式:省略了子句或表达式的一个必需部分。

Answer 1

我已将您的查询重构为：

  select cl.nom_cl
      from client cl
  where cl.id_client IN(select e.id_acheteur
  from enchere e 
  group by e.id_acheteur 
  having(  count(distinct (e.idobj)) IN (select count(distinct (e2.idobj)) from enchere e2) )  ) 

查询的select count(distinct (e2.idobj)) from enchere e2部分可能会检索多条记录，这也是您获得错误的原因。另外，这是count(distinct e.idobj)错了。应该是这样的：count(distinct (e.idobj))

Answer 2

真正的问题是：

  select cl.nom_cl
    from client cl
    where (cl.id_client IN(select e.id_acheteur
                from enchere e 
                group by e.id_acheteur 
                having(  count(distinct e.idobj) = (select count(distinct e2.idobj) from enchere e2 ))  ) )