是否可以在没有连接的情况下按计数（不同）进行分组？

Table 1 : (Company)

ID Name
1  A
2  B
3  C

Each company (pk = ID) can have one or more employees.

每个公司（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

Query :

查询：

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

CompanyID count(*)
1         3  
3         2

For this query, I want just one result with the count of distinct CompanyIDs. So, '2' in this case [Companies A and C].

对于此查询，我只想要一个具有不同CompanyID计数的结果。所以，在这种情况下，'2'[公司A和C]。

In short, I am looking for number of companies with 2 or more employees.

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

Is there anyway to get the result without a temp table or a join? I am using MySQL.

无论如何在没有临时表或连接的情况下获得结果？我正在使用MySQL。

2 个解决方案

#1

Yes:

是：

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

or for ranges:

或范围：

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

#2

Yes, it's possible with subqueries:

是的，子查询可以：

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

or:

要么：

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
      )

or perhaps if COUNT(*) is slow, you can use MIN() and MAX():

或者如果COUNT（*）很慢，你可以使用MIN（）和MAX（）：

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

#1