sql中有聚集函数sum,但只能计算数值型,如(本文中试验都是在scott用户下):
% i. Z/ R9 ]) w0 [) i SQL> select deptno,sum(sal) asal8 d2 s' S3 S% p/ ]6 z4 _
from emp a
7 Q4 ^( r3 I2 v* f# a. y$ F group by deptno;8 |- i( X" P+ a6 l
DEPTNO ASAL
7 [6 `5 A i3 s( X% l 30 9400
$ Z8 v: l* @# n$ ~ 20 10875) K, j# \; N& g& M7 \/ h' ^' ^
10 8750, Q) d! d# S4 x" T
有时要想分组查看每个部门的人员列表如下结果:
* X* T7 a+ f+ e Z DEPTNO ENAME3 A h5 c- }/ L( ]5 q- \
10 CLARK,KING,MILLER+ T; G6 E- ]& @, t6 F G
20 SMITH,FORD,ADAMS,SCOTT,JONES* T' J" i0 {- r
30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD i1 `. F# d4 `
标准sql没有提供这个方法。
! i& ~4 T8 k6 o, o2 e7 ~6 @ SQL> select deptno,sum(ename) ename
, y& t" ~1 q: E$ h$ b# [ from emp a
) C0 Y: Z4 f/ w+ r t group by deptno;
4 |0 {9 X- M0 ^ ` select deptno,sum(ename) ename( V& S& |6 U% l0 V6 Z3 S) ]" F
*
8 Y7 y6 J9 U9 ] 第 1 行出现错误:2 }) L8 B! Q; p$ B6 y0 E
ORA01722: 无效数字0 w2 ?6 T( I, R" U. f
oracle10g提供了一个函数wmsys.wm_concat可以实现:
. E* ^, D- ~1 g+ ` h% | SQL> select deptno,wmsys.wm_concat(ename) ename: C( n8 ?' r( M. t( u R1 ^% |* l: q3 e
from emp a
u% z2 I3 O3 _/ n3 ]) X group by deptno;
8 `- {3 k( h7 I DEPTNO ENAME# [% J! w4 M2 W S- d8 v- ]
10 CLARK,KING,MILLER3 o0 @7 K5 H( i$ ] x% T1 F, E
20 SMITH,FORD,ADAMS,SCOTT,JONES/ u+ D2 y1 R& n7 l1 b
30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD3 r* n" }5 I8 c. k3 r
正是想要的结果。
: s3 M( o6 g! \: N k8 Z 但是在10g以下版本怎么办呢?
* A. O$ t4 S6 Q/ U, o W; ~ 在oracle9i可以用connect by 实现,但成本很高:
. h4 f" Q' y; M( A; a& R1 W SQL> select deptno,substr(max(sys_connect_by_path(ename,',')),2) ename' d7 a: p4 B2 X+ H# }
from (select a.*,row_number()over(partition by deptno order by empno) rn from emp a )
. K3 T3 }" _5 v* l3 H# F7 j2 V) X group by deptno9 {; f6 C- H5 A8 Q4 |2 X9 m- F3 t" F! l
start with rn=1
9 P% O; e0 \6 g* X- g1 G connect by rn1=prior rn and deptno=prior deptno9 r" T1 F1 k( w+ \) a
order by deptno;- O/ v7 |. K6 Q! C+ X4 O
DEPTNO ENAME( ~4 J5 Q& u9 }
10 CLARK,KING,MILLER6 x# \! v9 ~4 W' s# H" [$ I
20 SMITH,JONES,SCOTT,ADAMS,FORD
$ W% N) K9 u1 R% @4 y 30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
% w$ M3 S( ^6 ]5 |/ y( f 结果也正确。这个sql参考了一下其他人的例子,并用到了分析函数。 |