SQL 反向变换结果集成一列|极客教程

SQL 反向变换结果集成一列，你想把一个查询结果合并成一列。例如，你希望返回 DEPTNO 等于 10 的全体员工的 ENAME、JOB 和 SAL，并且想把 3 列值合并成 1 列。你希望为每一个员工返回 3 行数据，员工之间以空行分隔开。你希望得到如下所示的结果集。

SQL 反向变换结果集成一列问题描述

你想把一个查询结果合并成一列。例如，你希望返回 DEPTNO 等于 10 的全体员工的 ENAME、JOB 和 SAL，并且想把 3 列值合并成 1 列。你希望为每一个员工返回 3 行数据，员工之间以空行分隔开。你希望得到如下所示的结果集。

EMPS
----------
CLARK
MANAGER
2450
　
KING
PRESIDENT
5000
　
MILLER
CLERK
1300

SQL 反向变换结果集成一列解决方案

关键在于使用笛卡儿积为每个员工返回 4 行数据。我们需要把每一列变成一行，并且在两个员工之间多留一个空白行。
DB2、Oracle 和 SQL Server
使用窗口函数 ROW_NUMBER OVER 基于 EMPNO 为每一行数据排名（1 ～ 4 行）。然后，使用 CASE 表达式把 3 列数据变成 1 列。

 1  select case rn
 2              when 1 then ename
 3              when 2 then job
 4              when 3 then cast(sal as char(4))
 5         end emps
 6    from (
 7  select e.ename,e.job,e.sal,
 8         row_number()over(partition by e.empno
 9                              order by e.empno) rn
10    from emp e,
11         (select *
12            from emp where job='CLERK') four_rows
13   where e.deptno=10
14         ) x

PostgreSQL 和 MySQL
本实例着重介绍如何使用窗口函数为每一行数据提供一个序号，该序号在之后的结果集变换操作中会被用到。在写作本书时，PostgreSQL 和 MySQL 尚未提供这样的窗口函数。

SQL 反向变换结果集成一列扩展知识

DB2、Oracle 和 SQL Server
首先使用窗口函数 ROW_NUMBER OVER 为 DEPTNO 等于 10 每一位员工生成一个序号。

select e.ename,e.job,e.sal,
       row_number()over(partition by e.empno
                            order by e.empno) rn
  from emp e
 where e.deptno=10
　
ENAME      JOB              SAL         RN
---------- --------- ---------- ----------
CLARK      MANAGER         2450          1
KING       PRESIDENT       5000          1
MILLER     CLERK           1300          1

此时，上面的序号其实并没有意义。我们按照 EMPNO 分区，因而 DEPTNO 等于 10 的所有行的序号都是 1。如果引入笛卡儿积，序号的作用就显现出来了，如下所示。

select e.ename,e.job,e.sal,
       row_number()over(partition by e.empno
                            order by e.empno) rn
  from emp e,
       (select *
          from emp where job='CLERK') four_rows
 where e.deptno=10
　
ENAME      JOB              SAL         RN
---------- --------- ---------- ----------
CLARK      MANAGER         2450          1
CLARK      MANAGER         2450          2
CLARK      MANAGER         2450          3
CLARK      MANAGER         2450          4
KING       PRESIDENT       5000          1
KING       PRESIDENT       5000          2
KING       PRESIDENT       5000          3
KING       PRESIDENT       5000          4
MILLER     CLERK           1300          1
MILLER     CLERK           1300          2
MILLER     CLERK           1300          3
MILLER     CLERK           1300          4

现在我们需要暂停一下，仔细推敲两个关键的要点。

class=”第2级无序列表”>每个员工的 RN 值不再是 1，现在它变成了从 1 到 4 循环出现的序列值，原因在于窗口函数会在 FROM 和 WHERE 子句之后才被评估执行。因此，按照 EMPNO 分区就导致当遇到一个新员工时，RN 值会被重置为 1。
内嵌视图 FOUR_ROWS 的存在只是为了返回一个包含 4 行数据的结果集。它的作用仅限于此。我们希望为每一列（ENAME、JOB 和 SAL）返回一行，然后再加上一个空行。

现在，最困难的工作已经完成了，剩下的就是使用 CASE 表达式把每个员工的 ENAME、JOB 和 SAL 并入一列（为了保证 CASE 成功执行，我们还需要把 SAL 转换成字符串）。

select case rn
            when 1 then ename
            when 2 then job
            when 3 then cast(sal as char(4))
       end emps
  from (
select e.ename,e.job,e.sal,
       row_number()over(partition by e.empno
                           order by e.empno) rn
  from emp e,
       (select *
          from emp where job='CLERK') four_rows
 where e.deptno=10
       ) x
　
    EMPS
    ----------
    CLARK
    MANAGER
    2450
　
    KING
    PRESIDENT
    5000
　
    MILLER
    CLERK
    1300