SQL查询实现：筛选经理并统计其所在部门员工数量

列出经理姓名以及该经理所在部门的员工数量。

测试表样例数据 #

EMP员工表结构及样例数据如下：

EMPNO---ENAME----JOB----------MGR----SAL---- DEPTNO
7839--- KING-----PRESIDENT---     ---5000-----10
7698----BLAKE----MANAGER-----7839----2850-----30
7782----CLARK----MANAGER-----7839----2450-----10
7566----JONES----MANAGER-----7839----2975-----20
7654----MARTIN---SALESMAN----7698----1250-----30
7499----ALLEN----SALESMAN----7698----1600-----30
7900----TURNER---SALESMAN----7698----1500-----30
7521----JAMES----CLERK-------7698----950------30
7902----WARD-----SALESMAN----7698----1250-----30
7902----FORD-----ANAYLYST----7566----3000-----20

原有代码问题 #

原有自连接查询没有对主体员工的岗位做过滤，会返回所有岗位员工及其对应部门人数，无法满足仅提取经理数据的要求，原有代码如下：

SELECT A.ENAME, COUNT(*)
FROM EMP A
JOIN EMP B
ON A.DEPTNO = B.DEPTNO
GROUP BY A.ENAME;

正确实现方案 #

方案1：自连接+条件过滤（全SQL版本兼容） #

核心逻辑是先将主体表A的范围限定为岗位是MANAGER的员工，再关联同部门所有员工做统计：

SELECT 
  A.ENAME AS 经理姓名,
  COUNT(B.EMPNO) AS 部门员工数量
FROM EMP A
JOIN EMP B
  ON A.DEPTNO = B.DEPTNO
WHERE A.JOB = 'MANAGER'
GROUP BY A.ENAME, A.DEPTNO;

注意点：

• 用COUNT(B.EMPNO)替代COUNT(*)，可以避免空值导致的统计误差
• 分组字段增加A.DEPTNO，符合SQL语法规范，也能避免不同部门出现同名经理时统计错误
• 针对给出的样例数据，执行后返回结果为：
  • BLAKE，对应30部门共6名员工
  • CLARK，对应10部门共2名员工
  • JONES，对应20部门共2名员工

方案2：窗口函数实现（高版本SQL环境支持） #

支持窗口函数的数据库环境下可以用这个写法，无需自连接，执行效率更高：

SELECT
  ENAME AS 经理姓名,
  DEPT_COUNT AS 部门员工数量
FROM (
  SELECT
    ENAME,
    JOB,
    COUNT(EMPNO) OVER (PARTITION BY DEPTNO) AS DEPT_COUNT
  FROM EMP
) t
WHERE JOB = 'MANAGER';

内容的提问来源于stack exchange，提问作者Andrew A