Oracle中一个简单交叉表的制作

现有以下两张表：

 

SQL> select * from emp;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980-12-17     800.00               20
 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20
 7839 KING       PRESIDENT       1981-11-17    5000.00               10
 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30
 7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20
 7900 JAMES      CLERK      7698 1981-12-3      950.00               30
 7902 FORD       ANALYST    7566 1981-12-3     3000.00               20
 7934 MILLER     CLERK      7782 1982-1-23     1300.00               10

14 rows selected

SQL> select *  from dept;

DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON

 

现在的需求是查处各部门中的各岗位的人数，这也是最基本的交叉表：

 

第一步：完成交叉表的动态列

SELECT deptno,job,
  (CASE job WHEN 'CLERK' THEN 1 ELSE NULL END) AS CLERK,
  (CASE job WHEN 'SALESMAN' THEN 1 ELSE NULL END) AS SALESMAN,
  (CASE job WHEN 'MANAGER' THEN 1 ELSE NULL END) AS MANAGER,
  (CASE job WHEN 'PRESIDENT' THEN 1 ELSE NULL END) AS PRESIDENT,
  (CASE job WHEN 'ANALYST' THEN 1 ELSE NULL END) AS ANALYST
FROM emp
GROUP BY deptno,job

 运行结果如下：

DEPTNO JOB            CLERK   SALESMAN    MANAGER  PRESIDENT    ANALYST
------ --------- ---------- ---------- ---------- ---------- ----------
    20 CLERK              1                                  
    30 SALESMAN                      1                       
    20 MANAGER                                  1            
    30 CLERK              1                                  
    10 PRESIDENT                                           1 
    30 MANAGER                                  1            
    10 CLERK              1                                  
    10 MANAGER                                  1            
    20 ANALYST                                                        1

9 rows selected

 第二部：使用count函数统计相同deptno相同job的人员

SELECT deptno,
  COUNT((CASE job WHEN 'CLERK' THEN 1 ELSE NULL END))  AS CLERK,
  COUNT((CASE job WHEN 'SALESMAN' THEN 1 ELSE NULL END)) AS SALESMAN,
  COUNT((CASE job WHEN 'MANAGER' THEN 1 ELSE NULL END)) AS MANAGER,
  COUNT((CASE job WHEN 'PRESIDENT' THEN 1 ELSE NULL END)) AS PRESIDENT,
  COUNT((CASE job WHEN 'ANALYST' THEN 1 ELSE NULL END)) AS ANALYST
FROM emp
GROUP BY deptno

 运行结果如下：

DEPTNO      CLERK   SALESMAN    MANAGER  PRESIDENT    ANALYST
------ ---------- ---------- ---------- ---------- ----------
    30          1          4          1          0          0
    20          1          0          1          0          1
    10          1          0          1          1          0

 这样就做成一个最简单的交叉表了。可以使用decode代替case...when...简化代码：

SELECT deptno AS 部门号,
  COUNT(decode(job,'CLERK',1,NULL)) AS CLERK,
  COUNT(decode(job,'SALESMAN',1,NULL)) AS SALESMAN,
  COUNT(decode(job,'MANAGER',1,NULL)) AS MANAGER,
  COUNT(decode(job,'PRESIDENT',1,NULL)) AS PRESIDENT,
  COUNT(decode(job,'ANALYST',1,NULL)) AS ANALYST
FROM emp
GROUP BY deptno

 运行结果同上。