Oracle SQL单查询实现按地区统计部门数与员工数（创建视图）

需求 #

编写Oracle SQL语句创建视图，通过单个查询展示每个地区（Loc）的部门数量，以及这些部门中的员工总数。

表结构与数据 #

Dept表 #

包含DEPTNO（部门编号）、DNAME（部门名称）、LOC（地区）字段，数据如下：

Emp表 #

包含EMPNO（员工编号）、ENAME（员工姓名）、JOB（职位）、MGR（上级编号）、HIREDATE（雇佣日期）、SAL（薪资）、COMM（佣金）、DEPTNO（所属部门编号）字段，数据如下：

EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
  9901 Kowalski   SALESMAN        9345 23-JAN-90        300        100         12
  7369 SMITH      CLERK           7902 17-MAR-80   15355.58                    20
  7499 ALLEN      SALESMAN        7698 20-MAR-81       1600        300         30
  7521 WARD       SALESMAN        7698 22-MAR-81       1250        500         30
  7566 JONES      MANAGER         7839 02-MAR-81   57103.26                    20
  7654 MARTIN     SALESMAN        7698 28-MAR-81       1250       1400         30
  7698 BLAKE      MANAGER         7839 01-MAR-81       2850                    30
  7782 CLARK      MANAGER         7839 09-MAR-81       2450                    10
  7788 SCOTT      ANALYST         7566 09-MAR-82   57583.15                    20
  7839 KING       PRESIDENT            17-MAR-81       5000                    10
  7844 TURNER     SALESMAN        7698 08-MAR-81       1500          0         30
  7876 ADAMS      CLERK           7788 12-MAR-83   21113.82                    20
  7900 JAMES      CLERK           7698 03-MAR-81        950                    30
  7902 FORD       ANALYST         7566 03-MAR-81   57583.15                    20
  7934 MILLER     CLERK           7782 23-MAR-82       1300                    10

期望输出 #

LOC               NO_DEP     NO_EMP
------------- ---------- ----------
NEW YORK               1         3
DALLAS                 1         1
CHICAGO                1         5
BOSTON                 2         6 

当前进展 #

已写出两个独立查询获取所需数据，但需要合并为单个查询输出：

SELECT DISTINCT d.Loc, Count(d.Deptno) AS No_Dep, d.Deptno
FROM Dept d
GROUP BY d.Loc, d.Deptno;

SELECT DISTINCT COUNT(e.Empno) OVER (PARTITION BY e.Deptno) AS No_Emp, e.Deptno
FROM Emp e;

正确解决方案 #

使用左连接关联两张表，按地区分组统计，同时用COUNT(DISTINCT)确保部门数统计准确，最终SQL语句如下：

SELECT d.LOC, COUNT(DISTINCT d.DEPTNO) AS NO_DEP, COUNT(e.EMPNO) AS NO_EMP  
FROM DEPT d  
LEFT JOIN EMP e ON d.DEPTNO = e.DEPTNO  
GROUP BY d.LOC;

内容的提问来源于stack exchange，提问作者Who knows