使用NOT IN抽取数据

Question

我是sql的新手，遇到了一个小问题。我有一个查询是这样的：

select cust_id, count(1) from all_customers
group by cust_id
having count(1)>4;

该查询给出了我想要的结果。

我需要对all_customers表中的所有客户进行新的查询，并排除从上面的查询中获得的结果。我试着这样做：

select * from all_customers
where cust_id NOT IN 
(
    select cust_id, count(1) from all_customers
    group by cust_id
    having count(1)>4
)

但是我得到了错误消息too many values。我做错了什么？

Answer 1

您应该去掉NOT IN子句中的聚合列。原因是您只比较cust_id列。还要注意，在使用NOT IN时，子查询应该始终返回单列。

select * 
from all_customers
where cust_id NOT IN 
      (
         select cust_id 
         from all_customers
         group by cust_id
         having count(1)>4
      )

Answer 2

子查询中有太多列，请尝试：

select * from all_customers
where cust_id NOT IN 
(select cust_id
 from all_customers
 group by cust_id
 having count(1)>4)