SQL 从Oracle中生成CSV格式的输出|极客教程

SQL 从Oracle中生成CSV格式的输出，你想把一个表里的数据转换成某种分隔列表形式（例如，以逗号作为分隔符）。例如，对于 EMP 表，你希望得到如下所示的结果集。

SQL 从Oracle中生成CSV格式的输出问题描述

你想把一个表里的数据转换成某种分隔列表形式（例如，以逗号作为分隔符）。例如，对于 EMP 表，你希望得到如下所示的结果集。

DEPTNO LIST
------ --------------------------------------
    10 MILLER,KING,CLARK
    20 FORD,ADAMS,SCOTT,JONES,SMITH
    30 JAMES,TURNER,BLAKE,MARTIN,WARD,ALLEN

假设你正在使用 Oracle 数据库（Oracle Database 10g 或后续的版本），并希望借助 MODEL 子句实现本问题的解决方案。

SQL 从Oracle中生成CSV格式的输出解决方案

本解决方案利用了 Oracle 的 MODEL 子句提供的迭代功能。这里用到的技巧是，使用窗口函数 ROW_NUMBER OVER 对每个 DEPTNO 对应的员工进行编号（按照 EMPNO 排序，不过按照哪个字段排序并不重要）。因为 MODEL 支持以数组形式访问行值，我们可以通过把序号减 1 实现对前一个数组元素的访问。因此，对于每一行，我们要创建一个列表，该列表包含了当前员工的名字，并加上编号小于当前员工的那些人的名字。

 1  select deptno,
 2         list
 3    from (
 4  select *
 5    from (
 6  select deptno,empno,ename,
 7         lag(deptno)over(partition by deptno
 8                             order by empno) prior_deptno
 9    from emp
10         )
11   model
12     dimension by
13     (
14       deptno,
15       row_number()over(partition by deptno order by empno) rn
16     )
17     measures
18     (
19       ename,
20       prior_deptno,cast(null as varchar2(60)) list,
21       count(*)over(partition by deptno) cnt,
22       row_number()over(partition by deptno order by empno) rnk
23     )
24     rules
25     (
26       list[any,any]
27       order by deptno,rn = case when prior_deptno[cv(),cv()] is null
28                                 then ename[cv( ),cv( )]
29                                 else ename[cv( ),cv( )]||','||
30                                      list[cv(),rnk[cv( ),cv( )]-1]
31                            end
32     )
33     )
34   where cnt = rn

SQL 从Oracle中生成CSV格式的输出扩展知识

首先使用窗口函数 LAG OVER 读取前一个员工（按照 EMPNO 排序）的 DEPTNO。结果按照 DEPTNO 分区，因此对于每个部门的第一个员工（按照 EMPNO 排序）返回值将是 Null，对于其余员工而言，返回值则是本部门的 DEPTNO。结果集如下所示。

select deptno,empno,ename,
       lag(deptno)over(partition by deptno
                           order by empno) prior_deptno
  from emp
　
DEPTNO      EMPNO ENAME  PRIOR_DEPTNO
------ ---------- ------ ------------
    10       7782 CLARK
    10       7839 KING             10
    10       7934 MILLER           10
    20       7369 SMITH
    20       7566 JONES            20
    20       7788 SCOTT            20
    20       7876 ADAMS            20
    20       7902 FORD             20
    30       7499 ALLEN
    30       7521 WARD             30
    30       7654 MARTIN           30
    30       7698 BLAKE            30
    30       7844 TURNER           30
    30       7900 JAMES            30

下一步仔细观察 MODEL 子句的 MEASURES 部分。MEASURES 列表里的项目是如下的几个数组。

ENAME：一个包含 EMP 表里全部 ENAME 值的数组。
PRIOR_DEPTNO：由窗口函数 LAG OVER 返回值构成的数组。
CNT：由每个 DEPTNO 对应的员工人数构成的数组。
RNK：每个 DEPTNO 分组内每个员工的编号（按照 EMPNO 排序）构成的数组。

数组索引是 DEPTNO 和 RN（在 DIMENSION BY 子句里调用窗口函数 ROW_NUMBER OVER 得到的返回值）。如果希望看到上述这些数组里实际包含了哪些值，只要注释掉 MODEL 子句的 RULES 部分代码并执行查询即可，如下所示。

select *
  from (
select deptno,empno,ename,
       lag(deptno)over(partition by deptno
                           order by empno) prior_deptno
  from emp
       )
 model
   dimension by
   (
     deptno,
     row_number()over(partition by deptno order by empno) rn
   )
   measures
   (
     ename,
     prior_deptno,cast(null as varchar2(60)) list,
     count(*)over(partition by deptno) cnt,
     row_number()over(partition by deptno order by empno) rnk
   )
   rules
   (
/*
     list[any,any]
     order by deptno,rn = case when prior_deptno[cv(),cv()] is null
                               then ename[cv(),cv()]
                               else ename[cv(),cv()]||','||
                                    list[cv(),rnk[cv( ),cv( )]-1]
                          end
*/
   )
 order by 1
　
DEPTNO  RN ENAME  PRIOR_DEPTNO LIST       CNT  RNK
------ --- ------ ------------ ---------- --- ----
    10   1 CLARK                            3    1
    10   2 KING             10              3    2
    10   3 MILLER           10              3    3
    20   1 SMITH                            5    1
    20   2 JONES            20              5    2
    20   4 ADAMS            20              5    4
    20   5 FORD             20              5    5
    20   3 SCOTT            20              5    3
    30   1 ALLEN                            6    1
    30   6 JAMES            30              6    6
    30   4 BLAKE            30              6    4
    30   3 MARTIN           30              6    3
    30   5 TURNER           30              6    5
    30   2 WARD             30              6    2

现在我们弄清楚 MODEL 子句里声明的那些项目了，接下来看一下 RULES 部分的代码。CASE 表达式负责评估 PRIOR_DEPTNO 的当前值。如果当前值是 Null，表明它是每个 DEPTNO 分组内的第一个员工，那么该员工的 ENAME 会被作为当前员工的 LIST。如果 PRIOR_DEPTNO 值不是 Null，那么就把前一个员工的 LIST 值附加上当前员工的名字（ENAME 数组），然后把结果作为当前员工的 LIST。针对 DEPTNO 分组内的每一行记录执行该 CASE 表达式操作的话，就会得到一个迭代的、逗号分隔的值列表（即 CSV 格式的输出）。如下所示的例子里打印出了中间结果。

select deptno,
       list
  from (
select *
  from (
select deptno,empno,ename,
       lag(deptno)over(partition by deptno
                           order by empno) prior_deptno
  from emp
       )
 model
   dimension by
   (
     deptno,
     row_number()over(partition by deptno order by empno) rn
   )
   measures
   (
     ename,
     prior_deptno,cast(null as varchar2(60)) list,
     count(*)over(partition by deptno) cnt,
     row_number()over(partition by deptno order by empno) rnk
   )
   rules
   (
     list[any,any]
     order by deptno,rn = case when prior_deptno[cv(),cv()] is null
                               then ename[cv(),cv()]
                               else ename[cv(),cv()]||','||
                                    list[cv(),rnk[cv( ),cv( )]-1]
                          end
   )
   )
　
DEPTNO LIST
------ ---------------------------------------
    10 CLARK
    10 KING,CLARK
    10 MILLER,KING,CLARK
    20 SMITH
    20 JONES,SMITH
    20 SCOTT,JONES,SMITH
    20 ADAMS,SCOTT,JONES,SMITH
    20 FORD,ADAMS,SCOTT,JONES,SMITH
    30 ALLEN
    30 WARD,ALLEN
    30 MARTIN,WARD,ALLEN
    30 BLAKE,MARTIN,WARD,ALLEN
    30 TURNER,BLAKE,MARTIN,WARD,ALLEN
    30 JAMES,TURNER,BLAKE,MARTIN,WARD,ALLEN

最后要过滤掉其他员工，只保留每个 DEPTNO 分组内的最后一个员工，这样才能确保每个 DEPTNO 都能得到一个完整的 CSV 列表。保存在 CNT 和 RN 数组里的值能实现这一点。RN 代表每个 DEPTNO 分组内按照 EMPNO 排序后得到的员工编号，因此每个 DEPTNO 分组内最后一个员工就是满足 CNT = RN 条件的那个员工，如下所示。

select deptno,
       list
  from (
select *
  from (
select deptno,empno,ename,
       lag(deptno)over(partition by deptno
                            order by empno) prior_deptno
  from emp
       )
 model
   dimension by
   (
     deptno,
     row_number()over(partition by deptno order by empno) rn
   )
   measures
   (
     ename,
     prior_deptno,cast(null as varchar2(60)) list,
     count(*)over(partition by deptno) cnt,
     row_number()over(partition by deptno order by empno) rnk
   )
   rules
   (
     list[any,any]
     order by deptno,rn = case when prior_deptno[cv(),cv()] is null
                               then ename[cv(),cv()]
                               else ename[cv(),cv()]||','||
                                    list[cv(),rnk[cv( ),cv( )]-1]
                          end
   )
   )
 where cnt = rn
　
DEPTNO LIST
------ ----------------------------------------
    10 MILLER,KING,CLARK
    20 FORD,ADAMS,SCOTT,JONES,SMITH
    30 JAMES,TURNER,BLAKE,MARTIN,WARD,ALLEN