SQL 创建分隔列表|极客教程

SQL 创建分隔列表，你想把行数据变成以某种符号分隔的列表，例如以逗号分隔，而不是常见的竖排的列数据形式。你希望转换下面的结果集。

SQL 创建分隔列表问题描述

你想把行数据变成以某种符号分隔的列表，例如以逗号分隔，而不是常见的竖排的列数据形式。你希望转换下面的结果集。

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

变成这样：

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

SQL 创建分隔列表解决方案

对于本问题而言，每一种数据库的解决方案都不同，关键在于如何充分利用数据库的内置函数。弄清楚数据库提供了哪些函数，我们才能充分利用数据库的功能，针对那些传统上 SQL 不擅长的问题探索出创造性的解决方案。
DB2
使用 WITH 子句递归地查询创建分隔列表。

 1   with x (deptno, cnt, list, empno, len)
 2     as (
 3 select deptno, count(*) over (partition by deptno),
 4        cast(ename as varchar(100)), empno, 1
 5   from emp
 6  union all
 7 select x.deptno, x.cnt, x.list ||','|| e.ename, e.empno, x.len+1
 8   from emp e, x
 9  where e.deptno = x.deptno
10    and e.empno > x. empno
11        )
12 select deptno,list
13   from x
14  where len = cnt

MySQL
使用内置函数 GROUP_CONCAT 创建分隔列表。

1 select deptno,
2        group_concat(ename order by empno separator, ',') as emps
3   from emp
4  group by deptno

Oracle
使用内置函数 SYS_CONNECT_BY_PATH 创建分隔列表。

 1  select deptno,
 2         ltrim(sys_connect_by_path(ename,','),',') emps
 3    from (
 4  select deptno,
 5         ename,
 6         row_number() over
 7                  (partition by deptno order by empno) rn,
 8         count(*) over
 9                  (partition by deptno) cnt
10    from emp
11         )
12   where level   = cnt
13   start with rn = 1
14 connect by prior deptno = deptno and prior rn = rn-1

PostgreSQL
PostgreSQL 没有提供用于创建分隔列表的标准内置函数，因而需要提前知道列表里有多少个元素。知道了最大的列表长度，就能确定在使用置换和字符串拼接等传统手段创建列表时需要附加多少个值。

 1 select deptno,
 2        rtrim(
 3            max(case when pos=1 then emps else '' end)||
 4            max(case when pos=2 then emps else '' end)||
 5            max(case when pos=3 then emps else '' end)||
 6            max(case when pos=4 then emps else '' end)||
 7            max(case when pos=5 then emps else '' end)||
 8            max(case when pos=6 then emps else '' end),','
 9        ) as emps
10   from (
11 select a.deptno,
12        a.ename||',' as emps,
13        d.cnt,
14        (select count(*) from emp b
15          where a.deptno=b.deptno and b.empno <= a.empno) as pos
16   from emp a,
17        (select deptno, count(ename) as cnt
18          from emp
19         group by deptno) d
20  where d.deptno=a.deptno
21        ) x
22  group by deptno
23  order by 1

SQL Server
使用 WITH 子句递归地查询创建分隔列表。

 1   with x (deptno, cnt, list, empno, len)
 2     as (
 3 select deptno, count(*) over (partition by deptno),
 4        cast(ename as varchar(100)),
 5        empno,
 6        1
 7   from emp
 8  union all
 9 select x.deptno, x.cnt,
10        cast(x.list + ',' + e.ename as varchar(100)),
11        e.empno, x.len+1
12   from emp e, x
13  where e.deptno = x.deptno
14    and e.empno > x. empno
15        )
16 select deptno,list
17   from x
18  where len = cnt
19  order by 1

SQL 创建分隔列表扩展知识

用 SQL 创建分隔列表之所以有用，是因为它是一个常见任务。然而，每种数据库的做法却各不相同。甚至，不同数据库的解决方案之间几乎没有相同之处。从使用递归、分层函数、经典的类型转换到数据聚合，各个数据库的做法迥异。
DB2 和 SQL Server
这两种数据库的解决方案仅在语法上略有不同（DB2 的字符串连接运算符是||，SQL Server 则是 +），具体做法完全相同。WITH 子句的第一个查询（UNION ALL 的前半部分）返回每位员工的下列信息：部门、员工编号、名字、ID 和常量 1（在这里，该常量没有任何作用）。递归处理发生在第二个查询（UNION ALL 的后半部分），并生成分隔列表。为了理解分隔列表的生成过程，我们来仔细观察该解决方案的一些代码片段。首先是 UNION ALL 的第二个查询的 SELECT 列表的第三项。

x.list ||','|| e.ename

然后是该查询的 WHERE 子句。

where e.deptno = x.deptno
  and e.empno > x.empno

本解决方案首先确保员工是同一个部门的。然后，对于 UNION ALL 的第一个查询返回的每一个员工，只要员工编号比自己大，就把名字附加在分隔列表的最后。这样就能确保不会把自己的名字附加到最后。表达式：

x.len+1

在每次一个员工被评估过之后为 LEN（从 1 开始）加上 1。如果累加值等于该部门的员工总数，我们就知道全部员工都被评估过了，分隔列表也就创建完成了。这是关键所在，它不仅标志着分隔列表创建完成，也能及时终止递归处理。

where len = cnt

MySQL
GROUP_CONCAT 函数可以完成所有的工作。它负责把传递给它的 ENAME 列拼接起来。GROUP_CONCAT 函数是一个聚合函数，因而查询语句里需要用到 GROUP BY。
Oracle
理解 Oracle 解决方案的第一步是把它拆开来看。执行内嵌视图（第 4 ～ 10 行），生成的结果集包括每位员工的下列信息：部门、名字，按照 EMPNO 升序排列得出的每位员工在各自部门的排名，以及本部门的员工总数。例如：

select deptno,
        ename,
        row_number() over
                  (partition by deptno order by empno) rn,
        count(*) over (partition by deptno) cnt
  from emp
　
DEPTNO ENAME      RN CNT
------ ---------- -- ---
    10 CLARK       1   3
    10 KING        2   3
    10 MILLER      3   3
    20 SMITH       1   5
    20 JONES       2   5
    20 SCOTT       3   5
    20 ADAMS       4   5
    20 FORD        5   5
    30 ALLEN       1   6
    30 WARD        2   6
    30 MARTIN      3   6
    30 BLAKE       4   6
    30 TURNER      5   6
    30 JAMES       6   6

排名（上述查询里别名为 RN）是为了方便遍历整棵树。由于 ROW_NUMBER 生成从 1 开始的连续数字序列，不会有重复数字，也不会有空隙，因此如果想参照前一行（或者父节点），只需要（把当前的 RN 值）减 1 即可。例如，3 前面的数字是 3 减去 1，结果是 2。在这里，2 是 3 的父节点，可以通过第 12 行观察到这一点。除此之外，下面的两行代码：

start with rn = 1
connect by prior deptno = deptno

指明 RN 等于 1 的节点即为每个 DEPTNO 的根节点，并为（RN 等于 1 的记录出现时）每一个新出现的部门创建一个单独的列表。
这时，我们应该停下来，再看一下 ROW_NUMBER 函数的 ORDER BY 部分。请记住，员工名字会按照 EMPNO 排名，列表也按照 EMPNO 的顺序生成。每个部门的员工总数会被计算出来（别名为 CNT），并用于确保只有那些包含部门内全体员工的列表才会被返回。之所以要这样做，是因为 SYS_CONNECT_BY_PATH 会循环生成列表，而我们也不希望得到不完整的列表。
对于层次查询，伪列 LEVEL 从 1 开始（对于不使用 CONNECT BY 的查询，LEVEL 是 0。但在 OracleDatabase 10g 数据库及后续版本里，LEVEL 必须和 CONNECT BY 同时出现），每当部门里的一个员工被评估（即层次遍历每深入一层）后，LEVEL 会加 1。因此，当 LEVEL 和 CNT 相等的时候，我们就知道循环走到了最后一个 EMPNO，这时就产生了一个完整的列表。

![](https://img.geek-docs.com/sql/tip.png)　`SYS_CONNECT_BY_PATH` 函数会在列表的前面也加上一个事先选定的分隔符（本例中是逗号）。有时这不符合我们的预期。本实例的解决方案里，我们调用 `LTRIM` 函数删除了列表开头的逗号。

PostgreSQL
PostgreSQL 解决方案要求事先知道所有部门里员工总数的最大值。执行内嵌视图（第 11 ～ 18 行）生成的结果集包括（每位员工的）部门、后面附加了逗号的名字、所属部门的员工总数以及 EMPNO 比他小的员工总数。

deptno  |  emps   | cnt | pos
--------+---------+-----+-----
     20 | SMITH,  |   5 |   1
     30 | ALLEN,  |   6 |   1
     30 | WARD,   |   6 |   2
     20 | JONES,  |   5 |   2
     30 | MARTIN, |   6 |   3
     30 | BLAKE,  |   6 |   4
     10 | CLARK,  |   3 |   1
     20 | SCOTT,  |   5 |   3
     10 | KING,   |   3 |   2
     30 | TURNER, |   6 |   5
     20 | ADAMS,  |   5 |   4
     30 | JAMES,  |   6 |   6
     20 | FORD,   |   5 |   5
     10 | MILLER, |   3 |   3

产生 POS 列的标量子查询（第 14 ～ 15 行）用于按照 EMPNO 来排列每一个员工。例如，下面这行代码。

max(case when pos = 1 then emps else '' end)||

上述代码评估 POS 是否等于 1。如果 POS 等于 1，CASE 表达式将返回员工名字，反之则返回 Null。
我们必须先查询整个表的数据，搞清楚一个列表里最多可能出现几个值。对于 EMP 表的数据而言，一个部门里最多有 6 个人，因此一个列表里最多会出现 6 项。
下一步就是开始创建列表。我们在内嵌视图返回的行数据之上（以 CASE 表达式的形式）执行一些条件逻辑运算来做到这一点。
列表里可能出现多个值，我们就必须写多个 CASE 表达式。
如果 POS 等于 1，当前的名字会被加入列表。第二个 CASE 表达式评估 POS 是否等于 2；如果是，则第二个名字会被附加在第一个后面。如果没有第二个名字，则会有一个额外的逗号附加在第一个名字后面（对于每一个不同的 POS 值，该处理都会重复一次，直至最后一个）。
这里的 MAX 函数调用不可省略，因为每个部门只需要生成一个列表（也可以调用 MIN 函数。本例中两者没有分别，因为对于每一个 CASE 条件运算，POS 只返回一个值）。无论何时调用了聚合函数，SELECT 列表里不涉及聚合运算的项目必须出现在 GROUP BY 子句里。这一点确保了每个 SELECT 列表里不涉及聚合运算的项目只会出现一行。
注意，我们需要 RTRIM 函数来删除末尾的逗号，逗号的数目总是等于列表里可能出现的值的最大个数（本例中为 6）。