SQL 创建稀疏矩阵|极客教程

SQL 创建稀疏矩阵，你想创建一个稀疏矩阵，把 EMP 表的 DEPTNO 和 JOB 列变换成如下所示的结果集。

SQL 创建稀疏矩阵问题描述

你想创建一个稀疏矩阵，把 EMP 表的 DEPTNO 和 JOB 列变换成如下所示的结果集。

D10        D20        D30        CLERKS MGRS  PREZ ANALS SALES
---------- ---------- ---------- ------ ----- ---- ----- ------
           SMITH                 SMITH
                      ALLEN                              ALLEN
                      WARD                               WARD
           JONES                        JONES
                      MARTIN                             MARTIN
                      BLAKE             BLAKE
CLARK                                   CLARK
           SCOTT                                   SCOTT
KING                                          KING
                      TURNER                             TURNER
           ADAMS                 ADAMS
                      JAMES      JAMES
           FORD                                    FORD
MILLER                           MILLER

SQL 创建稀疏矩阵解决方案

使用一组 CASE 表达式创建一个稀有矩阵，实现从行到列的变换。

1  select case deptno when 10 then ename end as d10,
2         case deptno when 20 then ename end as d20,
3         case deptno when 30 then ename end as d30,
4         case job when 'CLERK'     then ename end as clerks,
5         case job when 'MANAGER'   then ename end as mgrs,
6         case job when 'PRESIDENT' then ename end as prez,
7         case job when 'ANALYST'   then ename end as anals,
8         case job when 'SALESMAN'  then ename end as sales
9    from emp

SQL 创建稀疏矩阵扩展知识

要把 DEPTNO 和 JOB 值从行形式变换成列形式，只要使用 CASE 表达式把全部可能的值逐一评估一遍即可。这是本解决方案的要点所在。除此之外，如果希望删除一些 Null 行，以便让整个报表显得“紧密”一些，我们还需要按照某个列做分组操作。例如，使用窗口函数 ROW_NUMBER OVER 为每个 DEPTNO 对应的每个员工生成一个序号，然后使用聚合函数 MAX 删除一些 Null 值。

select max(case deptno when 10 then ename end) d10,
       max(case deptno when 20 then ename end) d20,
       max(case deptno when 30 then ename end) d30,
       max(case job when 'CLERK'     then ename end) clerks,
       max(case job when 'MANAGER'   then ename end) mgrs,
       max(case job when 'PRESIDENT' then ename end) prez,
       max(case job when 'ANALYST'   then ename end) anals,
       max(case job when 'SALESMAN' then ename end) sales
  from (
select deptno, job, ename,
       row_number()over(partition by deptno order by empno) rn
  from emp
       ) x
 group by rn
　
D10        D20        D30        CLERKS MGRS  PREZ ANALS SALES
---------- ---------- ---------- ------ ----- ---- ----- ------
CLARK      SMITH      ALLEN      SMITH  CLARK             ALLEN
KING       JONES      WARD              JONES KING         WARD
MILLER     SCOTT      MARTIN     MILLER            SCOTT MARTIN
           ADAMS      BLAKE      ADAMS  BLAKE
           FORD       TURNER                       FORD  TURNER
                      JAMES      JAMES