SQL 反向变换结果集成一列

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

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

你想把一个查询结果合并成一列。例如,你希望返回 DEPTNO 等于 10 的全体员工的 ENAMEJOBSAL,并且想把 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

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

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

DB2、Oracle 和 SQL Server
首先使用窗口函数 ROW_NUMBER OVERDEPTNO 等于 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 循环出现的序列值,原因在于窗口函数会在 FROMWHERE 子句之后才被评估执行。因此,按照 EMPNO 分区就导致当遇到一个新员工时,RN 值会被重置为 1。
  • 内嵌视图 FOUR_ROWS 的存在只是为了返回一个包含 4 行数据的结果集。它的作用仅限于此。我们希望为每一列(ENAMEJOBSAL)返回一行,然后再加上一个空行。

现在,最困难的工作已经完成了,剩下的就是使用 CASE 表达式把每个员工的 ENAMEJOBSAL 并入一列(为了保证 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

Python教程

Java教程

Web教程

数据库教程

图形图像教程

大数据教程

开发工具教程

计算机教程