SQL - WHERE AGGREGATE>1

Question

想象一下，我有一个包含 {id,username,firstname,lastname} 的客户数据库表，

如果我想查找有多少个不同名字的实例，我可以这样做：

select firstname,count(*) from Customers group by 2 order by 1;

   username | count(*)
   ===================
   bob      |   1
   jeff     |   2
   adam     |   5

如何编写相同的查询以仅返回出现次数超过的名字一次？ 即在上面的示例中仅返回 jeff 和 adam 的行。

Answer 1

您需要 having 子句，如下所示：

select 
    firstname,
    count(*) 
from Customers 
group by firstname
having count(*) > 1
order by 1

Answer 2

我应该说，group by 2 order by 1 太糟糕了。 如果支持，请使用正确的列名称：这将大大提高可读性。

考虑到这一点，

select firstname, count(*) c 
from Customers 
group by firstname  
having count(*) > 1 -- Kudos to Shannon
order by c;

Answer 3

这就是 HAVING 子句的作用。 我不确定这是否适用于 informix，但请尝试一下：

select firstname, count(*) 
from Customers 
group by firstname
HAVING COUNT(*) > 1