SQL - 计数 WHERE AGGREGATE>1

Question

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

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

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

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

如何计算多次出现的名字的数量？在伪 sql 中，它会是这样的：

select
    COUNT(
        firstname,
        count(*) as num_occurrences
    )
from 
    Customers 
group by 2 
having num_occurrences > 1;

Answer 1

您的想法是正确的：

SELECT COUNT(*)
FROM (
  SELECT firstname
  FROM Customers
  GROUP BY firstname
  HAVING COUNT(*) >= 2
)

子查询对出现多次的名字进行计数。然后你数一下所有这些名字。 HAVING 子句允许您按聚合进行过滤。它类似于 WHERE 子句，只不过您可以使用聚合函数。

Answer 2

不需要子查询。

尝试：

SELECT firstname, COUNT(*)
  FROM Customers
 GROUP BY firstname
HAVING COUNT(*) > 1
 ORDER BY firstname

或者，按最常见的名称排序：

SELECT firstname, COUNT(*) AS custcount
  FROM Customers
 GROUP BY firstname
HAVING COUNT(*) > 1
 ORDER BY custcount DESC;

Answer 3

这样就可以做到：

select count(username)
  from (select username
          from Customers
         group by username
        having count(*) > 1);