是否有可能在没有连接的情况下按计数(distinct)执行组

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

对于这个查询，我只想要一个包含不同companyid计数的结果。所以，这里的"2"指的是A公司和C公司。

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

有没有办法得到没有临时表或连接的结果?

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