极客教程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