有没有可能通过计数（不同）没有加入一个组？

Question

Table 1 : (Company) 

ID Name 
1 A 
2 B 
3 C 

每个公司（pk = ID）都可以有一个或多个员工。

Table 2 : (Employee) (CompanyID referencing ID) 

CompanyID EmpID Name 
1   1  Joe 
1   2  Doe 
1   3  Boe 
2   4  Lou 
3   5  Su 
3   6  Ram 

查询：

select CompanyID, count(*) from Employee group by CompanyID having count(*) > 1; # Lists companies and their counts. 

CompanyID count(*) 
1   3 
3   2 

对于此查询，我只想要一个结果，具有鲜明的CompanyIDs的计数。因此，在这种情况下，'2'[公司A和C]。

总之，我正在寻找拥有2名或更多员工的公司数量。

反正有没有临时表或连接得到结果？我正在使用MySQL。

Answer 1

是：

select count(*) from 
(select CompanyID from Employee group by CompanyID having count(*) > 1) v 

或范围：

select count(*) from 
(select CompanyID from Employee group by CompanyID 
having count(*) >= 5 and count(*) < 10) v 

Answer 2

是的，这可能与子查询：

SELECT COUNT(*) 
FROM 
    (SELECT 1 
    FROM Employee 
    GROUP BY CompanyID 
    HAVING COUNT(*) > 1 
) AS grp 

或：

SELECT COUNT(DISTINCT CompanyID) 
FROM Employee AS e 
WHERE EXISTS 
     (SELECT * 
     FROM Employee AS e2 
     WHERE e2.CompanyID = e.CompanyId 
      AND e2.EmpID <> e.EmpID 
    ) 

或许如果COUNT(*)很慢，你可以使用MIN()和MAX()：

SELECT COUNT(*) 
FROM 
    (SELECT 1 
    FROM Employee 
    GROUP BY CompanyID 
    HAVING MAX(EmpID) > MIN(EmpId) 
) AS grp