在SQL Server中获得不同的计数

I have three tables:

我有三张桌子：

companies
公司
customers for those companies
这些公司的客户
addresses for each customer
每个客户的地址

I want to know how many companies in my database has the accounts with more that 100 addresses linked to them.

我想知道我的数据库中有多少公司拥有超过100个与之相关联的地址的帐户。

I tried this but it didn't work:

我尝试了这个，但它不起作用：

SELECT  
    COUNT(DISTINCT c.Id) 
FROM 
    Customers cu
INNER JOIN 
    Addresses ad ON ad.Customer = cu.Id
INNER JOIN 
    Companies c ON cu.company = c.Id
GROUP BY 
    cu.ID
HAVING 
    COUNT(ad.ID) > 100

1 个解决方案

#1

You need two levels of aggregation for this query. Here is one method:

此查询需要两个级别的聚合。这是一种方法：

SELECT COUNT(*)
FROM (SELECT cu.company, COUNT(DISTINCT ad.id) as num_accresses
      FROM Customers cu 
         INNER JOIN Addresses ad ON ad.Customer = cu.Id
      GROUP BY cu.company
      HAVING COUNT(DISTINCT ad.id) > 100
     ) cua;

The inner query returns the companies that have more than 100 addresses. Note the use of COUNT(DISTINCT). Presumably, two "customers" could have the same addresses.

内部查询返回具有超过100个地址的公司。注意使用COUNT（DISTINCT）。据推测，两个“客户”可能拥有相同的地址。

Also, the companies table is not needed. The identifying information is in customers. You are only looking for a count, so the table is not necessary.

此外，不需要公司表。识别信息在客户中。你只是在寻找一个计数，所以这个表是没有必要的。

#1