Oracle技术：如何在ORACLE中聚集合并字符串

会计考友

sql中有聚集函数sum,但只能计算数值型，如（本文中试验都是在scott用户下）：
　　SQL> select deptno,sum(sal) asal
　　from emp a
$ C, d' P; R9 S3 m" E+ l& d　　group by deptno;
4 b/ c0 o& k$ b' ]; |1 U/ `　　DEPTNO ASAL
　　30 9400
　　20 10875
　　10 8750
　　有时要想分组查看每个部门的人员列表如下结果：
　　DEPTNO ENAME
& Z  J7 P2 x7 Y( e5 h　　10 CLARK,KING,MILLER
　　20 SMITH,FORD,ADAMS,SCOTT,JONES
　　30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD
　　标准sql没有提供这个方法。
3 x; L% a$ R. z1 W5 \% |　　SQL> select deptno,sum(ename) ename
  W" H* [8 T2 L3 `1 S' L+ C5 S　　from emp a
+ B% u& b+ o% ^& K/ b( ?　　group by deptno;
% X! K0 w, y# }) I; w　　select deptno,sum(ename) ename
　　*
% @( X0 `& A3 S# P* M8 W) r　　第 1 行出现错误:
　　ORA01722: 无效数字
& ~  X% E( g7 U2 s% Y8 F0 _( v　　oracle10g提供了一个函数wmsys.wm_concat可以实现：
1 f: |) j- W7 w& i: @　　SQL> select deptno,wmsys.wm_concat(ename) ename
, N* c3 C% `# X. I! R. @1 m& t　　from emp a
2 k6 u: k! |; |8 [- C$ o　　group by deptno;
　　DEPTNO ENAME
( A( y# Q1 J1 u, c; ?# l# ~4 h　　10 CLARK,KING,MILLER
$ o! p: ?  |3 }% V( |; M+ E$ Y　　20 SMITH,FORD,ADAMS,SCOTT,JONES
　　30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD
9 o9 N' D" k8 c# Q6 h0 Q" f+ q　　正是想要的结果。
+ v, {" O$ d7 ~  e　　但是在10g以下版本怎么办呢？
! G! u5 Y' }1 |8 K. h. U　　在oracle9i可以用connect by 实现，但成本很高：
1 v* O# @1 \7 F* [　　SQL> select deptno,substr(max(sys_connect_by_path(ename,',')),2) ename
　　from (select a.*,row_number()over(partition by deptno order by empno) rn from emp a )
7 j$ _4 i* h, q7 Y- g　　group by deptno
　　start with rn=1
　　connect by rn1=prior rn and deptno=prior deptno
  m! P9 n: Y9 p& I3 W( K! N　　order by deptno;
　　DEPTNO ENAME
　　10 CLARK,KING,MILLER
　　20 SMITH,JONES,SCOTT,ADAMS,FORD
　　30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
& Z+ x+ e. i1 V8 n- h2 u　　结果也正确。这个sql参考了一下其他人的例子，并用到了分析函数。