SQL 变换结果集成多行|极客教程

SQL 变换结果集成多行，你想把行变换成列，并根据指定列的值来决定每一行原来的数据要被划分到新数据的哪一列。然而，与前一个实例不同的是，你需要输出的结果不止一行。

SQL 变换结果集成多行问题描述

你想把行变换成列，并根据指定列的值来决定每一行原来的数据要被划分到新数据的哪一列。然而，与前一个实例不同的是，你需要输出的结果不止一行。
例如，你希望返回每个员工和他们的职位（对应 EMP 表的 JOB 列），你先查询得到了下面的结果集。

JOB       ENAME
--------- ----------
ANALYST   SCOTT
ANALYST   FORD
CLERK     SMITH
CLERK     ADAMS
CLERK     MILLER
CLERK     JAMES
MANAGER   JONES
MANAGER   CLARK
MANAGER   BLAKE
PRESIDENT KING
SALESMAN  ALLEN
SALESMAN  MARTIN
SALESMAN  TURNER
SALESMAN  WARD

你希望格式化上述结果集，为每一种职位生成一列新数据。

CLERKS ANALYSTS MGRS  PREZ SALES
------ -------- ----- ---- ------
MILLER FORD     CLARK KING TURNER
JAMES  SCOTT    BLAKE      MARTIN
ADAMS           JONES      WARD
SMITH                      ALLEN

SQL 变换结果集成多行解决方案

与 12.1 节中的实例不同，本实例最后输出的结果集会多于一行。因此，12.1 节中实例的做法不适用于本例，因为如果针对每一个 JOB 列执行 MAX(ENAME) 的话，会导致每个 JOB 只对应一个 ENAME（也就是说，就会像 12.1 节中的实例那样只返回一行结果）。为了解决这一问题，我们必须保持每一个 JOB/ENAME 组合的唯一性。这样一来，再调用聚合函数剔除掉 Null 的话，就不会丢失任何一个 ENAME 了。
DB2、Oracle 和 SQL Server
使用窗口函数 ROW_NUMBER OVER 确保每一个 JOB/ENAME 组合都是唯一的。针对窗口函数的返回值执行 GROUP BY，并使用 CASE 表达式和聚合函数 MAX 实现结果集变换。

 1  select max(case when job='CLERK'
 2                  then ename else null end) as clerks,
 3         max(case when job='ANALYST'
 4                  then ename else null end) as analysts,
 5         max(case when job='MANAGER'
 6                  then ename else null end) as mgrs,
 7         max(case when job='PRESIDENT'
 8                  then ename else null end) as prez,
 9         max(case when job='SALESMAN'
10                  then ename else null end) as sales
11    from (
12  select job,
13         ename,
14         row_number()over(partition by job order by ename) rn
15    from emp
16         ) x
17   group by rn

PostgreSQL 和 MySQL
使用标量子查询基于 EMPNO 为每个员工排序。针对标量子查询的返回值执行 GROUP BY，并使用 CASE 表达式和聚合函数 MAX 实现结果集变换。

 1  select max(case when job='CLERK'
 2                  then ename else null end) as clerks,
 3         max(case when job='ANALYST'
 4                  then ename else null end) as analysts,
 5         max(case when job='MANAGER'
 6                  then ename else null end) as mgrs,
 7         max(case when job='PRESIDENT'
 8                  then ename else null end) as prez,
 9         max(case when job='SALESMAN'
10                  then ename else null end) as sales
11    from (
12  select e.job,
13         e.ename,
14         (select count(*) from emp d
15           where e.job=d.job and e.empno < d.empno) as rnk
16    from emp e
17         ) x
18   group by rnk

SQL 变换结果集成多行扩展知识

DB2、Oracle 和 SQL Server
首先，使用窗口函数 ROW_NUMBER OVER 确保每一个 JOB/ENAME 组合的唯一性。

select job,
       ename,
       row_number()over(partition by job order by ename) rn
  from emp
　
JOB       ENAME              RN
--------- ---------- ----------
ANALYST   FORD                1
ANALYST   SCOTT               2
CLERK     ADAMS               1
CLERK     JAMES               2
CLERK     MILLER              3
CLERK     SMITH               4
MANAGER   BLAKE               1
MANAGER   CLARK               2
MANAGER   JONES               3
PRESIDENT KING                1
SALESMAN  ALLEN               1
SALESMAN  MARTIN              2
SALESMAN  TURNER              3
SALESMAN  WARD                4

对于一种给定的职位，为其中的每一个 ENAME 安排一个唯一的“行编号”，这样即使出现了两个员工具有相同名字和职位的情况也不会有问题。这样做是为了既能基于行编号（RN）分组，又不会因为使用了 MAX 而遗漏掉任何一个员工。这是解决本问题最重要的一步。如果没有这一步，外层查询的聚合操作会剔除掉必要的行。试想一下，如果仍然采用 12.1 节中实例的做法，而不使用 ROW_NUMBER OVER 函数的话，会得到怎样的结果集。

select max(case when job='CLERK'
                then ename else null end) as clerks,
       max(case when job='ANALYST'
                then ename else null end) as analysts,
       max(case when job='MANAGER'
                then ename else null end) as mgrs,
       max(case when job='PRESIDENT'
                then ename else null end) as prez,
       max(case when job='SALESMAN'
                then ename else null end) as sales
  from emp
　
CLERKS     ANALYSTS   MGRS       PREZ       SALES
---------- ---------- ---------- ---------- ----------
SMITH      SCOTT      JONES      KING       WARD

很不幸，每一种 JOB 只会返回一行：ENAME 值最大的那个员工会被返回。做结果集变换的时候，使用 MIN 或 MAX 是为了从结果集里剔除掉 Null 值，而不是为了过滤掉一些 ENAME。继续阅读下面的讲解内容，相信你会更加清楚地理解这种状况是如何产生的。
下一步使用 CASE 表达式把 ENAME 分别放入各自的列（JOB）。

select rn,
       case when job='CLERK'
            then ename else null end as clerks,
       case when job='ANALYST'
            then ename else null end as analysts,
       case when job='MANAGER'
            then ename else null end as mgrs,
       case when job='PRESIDENT'
            then ename else null end as prez,
       case when job='SALESMAN'
            then ename else null end as sales
  from (
select job,
       ename,
       row_number()over(partition by job order by ename) rn
  from emp
       ) x
　
RN CLERKS     ANALYSTS   MGRS       PREZ       SALES
-- ---------- ---------- ---------- ---------- ----------
 1            FORD
 2            SCOTT
 1 ADAMS
 2 JAMES
 3 MILLER
 4 SMITH
 1                       BLAKE
 2                       CLARK
 3                       JONES
 1                                  KING
 1                                             ALLEN
 2                                             MARTIN
 3                                             TURNER
 4                                             WARD

现在，行已经变成了列，最后需要剔除掉 Null 值以提高结果集的可读性。为了剔除掉 Null，需要调用聚合函数 MAX，并基于 RN 执行 GROUP BY。（这里也可以使用 MIN 函数。选择 MAX 或者 MIN 没有区别，因为每个分组只包含一个值。）每个 RN/JOB/ENAME 组合只包含一个值。基于 RN 执行 GROUP BY，并且针对 CASE 表达式的返回值调用 MAX 函数，这能够确保每一次调用 MAX 函数都会从一个分组中取出一个 ENAME，而该分组内除了该 ENAME 之外，其他值都是 Null。

select max(case when job='CLERK'
                then ename else null end) as clerks,
       max(case when job='ANALYST'
                then ename else null end) as analysts,
       max(case when job='MANAGER'
                then ename else null end) as mgrs,
       max(case when job='PRESIDENT'
                then ename else null end) as prez,
       max(case when job='SALESMAN'
                then ename else null end) as sales
  from (
select job,
       ename,
       row_number()over(partition by job order by ename) rn
  from emp
       ) x
 group by rn
　
CLERKS ANALYSTS MGRS  PREZ SALES
------ -------- ----- ---- ------
MILLER FORD     CLARK KING TURNER
JAMES  SCOTT    BLAKE      MARTIN
ADAMS           JONES      WARD
SMITH                      ALLEN

使用 ROW_NUMBER OVER 生成唯一的组合对于格式化查询结果集非常有用。考虑如下所示的查询，它创建了一个分别以 DEPTNO 和 JOB 为维度展示员工的稀疏矩阵报表。

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

通过改变分组列（即上述 SELECT 列表里的非聚合项），可以产生出不同格式的报表。这值得我们花时间去做一些实验，并深入理解 GROUP BY 子句包含的项目发生了改动之后，输出结果的格式将会发生什么变化。
PostgreSQL 和 MySQL
对于这两种数据库而言，做法和其他数据库基本相同，只是创建唯一 JOB/ENAME 组合的方法有所不同。首先使用标量子查询为每一个 JOB/ENAME 组合提供一个“行编号”或“排名”。

select e.job,
       e.ename,
       (select count(*) from emp d
         where e.job=d.job and e.empno < d.empno) as rnk
  from emp e
　
JOB       ENAME             RNK
--------- ---------- ----------
CLERK     SMITH               3
SALESMAN  ALLEN               3
SALESMAN  WARD                2
MANAGER   JONES               2
SALESMAN  MARTIN              1
MANAGER   BLAKE               1
MANAGER   CLARK               0
ANALYST   SCOTT               1
PRESIDENT KING                0
SALESMAN  TURNER              0
CLERK     ADAMS               2
CLERK     JAMES               1
ANALYST   FORD                0
CLERK     MILLER              0

为每一个 JOB/ENAME 组合安排一个唯一的“行编号”，这可以确保每一行都是唯一的。即使有两个员工名字相同，其职位也恰好一样，他们也不会共享同一个“行编号”。这是解决本问题最重要的一步。如果没有这一步，外层查询的聚合操作会剔除掉必要的行。试想一下，如果仍然采用 12.1 节中实例的做法，而不为每一个 JOB/ENAME 组合安排一个唯一的“行编号”的话，会得到怎样的结果集。

select max(case when job='CLERK'
                then ename else null end) as clerks,
       max(case when job='ANALYST'
                then ename else null end) as analysts,
       max(case when job='MANAGER'
                then ename else null end) as mgrs,
       max(case when job='PRESIDENT'
                then ename else null end) as prez,
       max(case when job='SALESMAN'
                then ename else null end) as sales
  from emp
　
CLERKS     ANALYSTS   MGRS       PREZ       SALES
---------- ---------- ---------- ---------- ----------
SMITH      SCOTT      JONES      KING       WARD

很不幸，每一种 JOB 只会返回一行：ENAME 值最大的那个员工会被返回。做结果集变换的时候，使用 MIN 或 MAX 是为了从结果集里剔除掉 Null 值，而不是为了过滤掉一些 ENAME。
现在，我们已经充分了解了“行编号”的作用，可以接着做下一步了。下一步使用 CASE 表达式把 ENAME 分别放入各自的列（JOB）。

select rnk,
       case when job='CLERK'
            then ename else null end as clerks,
       case when job='ANALYST'
            then ename else null end as analysts,
       case when job='MANAGER'
            then ename else null end as mgrs,
       case when job='PRESIDENT'
            then ename else null end as prez,
       case when job='SALESMAN'
            then ename else null end as sales
  from (
select e.job,
       e.ename,
       (select count(*) from emp d
         where e.job=d.job and e.empno < d.empno) as rnk
  from emp e
       ) x
　
RNK CLERKS ANALYSTS MGRS  PREZ SALES
--- ------ -------- ----- ---- ----------
  3 SMITH
  3                            ALLEN
  2                            WARD
  2                 JONES
  1                            MARTIN
  1                 BLAKE
  0                 CLARK
  1        SCOTT
  0                       KING
  0                            TURNER
  2 ADAMS
  1 JAMES
  0        FORD
  0 MILLER

现在，行已经变成了列，最后需要剔除掉 Null 值以提高结果集的可读性。为了剔除掉 Null，要调用聚合函数 MAX，并且基于 RNK 执行 GROUP BY。（这里也可以使用 MIN 函数，选择 MAX 或者 MIN 没有区别。）每个 RNK/JOB/ENAME 组合只存在一个值，因此调用了聚合函数之后，Null 值会被剔除掉。

select max(case when job='CLERK'
                then ename else null end) as clerks,
       max(case when job='ANALYST'
                then ename else null end) as analysts,
       max(case when job='MANAGER'
                then ename else null end) as mgrs,
       max(case when job='PRESIDENT'
                then ename else null end) as prez,
       max(case when job='SALESMAN'
                then ename else null end) as sales
  from (
select e.job,
       e.ename,
       (select count(*) from emp d
         where e.job=d.job and e.empno < d.empno) as rnk
  from emp e
       ) x
 group by rnk
　
CLERKS ANALYSTS MGRS  PREZ SALES
------ -------- ----- ---- ------
MILLER FORD     CLARK KING TURNER
JAMES  SCOTT    BLAKE      MARTIN
ADAMS           JONES      WARD
SMITH                      ALLEN