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博客 ” ，链接：https://blog.itpub.net/13387766/viewspace-482020/ ，如需转载，请注明出处，否则将追究法律责任。
