stragg和sys_connect_by_path

select t.code,substr(max(sys_connect_by_path(t.sz,',')),2) sz
from
(select  code,sz,row_number()over(partition by code order by sz) rn from (select * from prdsz where rownum<100)) t
start with t.rn=1
connect by t.code=prior t.code
and t.rn-1=prior t.rn
group by t.code;

 select code,replace(stragg(sz),chr(10),',') sz  from (select * from prdsz where rownum<100)
  group by code;

这两个语句的效果一样，都是把每种产品所有的尺码用','连接起来。

注:stragg是Tom写的连接函数。

SQL> select m.dname,substr(max(sys_connect_by_path(m.ename,',')),2) mix_ename
  2  from (
  3  select t.*,row_number() over(partition by t.dname order by ename) rn
  4  from (
  5  select d.dname ,e.ename from dept_and_emp d,emp e where d.deptno=e.deptno) t
  6  ) m
  7  start with m.rn=1
  8  connect by m.dname=prior m.dname
  9  and  m.rn-1=prior  m.rn
 10  group by m.dname;

DNAME          MIX_ENAME
-------------- --------------------------------------------------------------------------------
ACCOUNTING     CLARK,KING,MILLER
RESEARCH       ADAMS,FORD,JONES,SCOTT,SMITH
SALES          ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13387766/viewspace-482020/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13387766/viewspace-482020/