这比较短,而且执行速度可能也更快
SELECT e1.empname, d.deptname
from (
SELECT e2.deptid
FROM employee AS e2
GROUP BY e2.deptid
HAVING COUNT(e2.empid) >= 4
) G
inner join employee AS e1 on e1.deptid = G.deptid
INNER JOIN department AS d on d.deptid = G.deptid
ORDER BY e1.empname;
从分组开始。您不需要内部查询中的COUNT。然后,联接两个表只是为了获得名称。
是因为一旦计数完成,我们已经知道