OracleÖкϲ¢×Ö·û´®×ܽá

ÔÚÊý¾Ý¿âÖо­³£ÒªºÏ²¢×Ö·û´®£¬¶øºÏ²¢×Ö·û´®µÄ·½·¨Óкܶ࣬ÏÖÔÚ×ܽáÈçÏ£º
--´´½¨»á»°¼¶ÁÙʱ±í
create global temporary table TMPA
(
  ID   INTEGER,
  NAME VARCHAR2(10)
)
on commit preserve rows;
--²åÈë¼Ç¼
insert into tmpa select 1,'aa' from dual;
insert into tmpa select 1,'bb' from dual;
insert into tmpa select 1,'cc' from dual;
insert into tmpa select 2,'dd' from dual;
insert into tmpa select 2,'ee' from dual;
insert into tmpa select 3,'ff' from dual;
commit;
--1¡¢sys_conect_by_path·½·¨
select id,max(ltrim(sys_connect_by_path(name,','),',')) as group_name
from
(
select a.*,row_number()over(partition by id order by name) as row_num
from tmpa a
) a
group by id
start with row_num=1
connect by prior id=id and prior row_num= row_num-1
--2¡¢wm_concat·½·¨
select id,wm_concat(name) as group_name from tmpa
group by id;
--3¡¢×Ô¶¨Ò庯Êý·¨
--¶¨Ò庯Êý
create function group_concat(vid number)
return varchar2
as
vResult varchar2(100);
begin
for cur in (select name from tmpa where id=vid) loop
vResult := vResult||cur.name;
end loop;
return vResult;
end;
--²éѯ
select id,group_concat(id) as group_name from tmpa
group by id;
--²éѯµÄ½á¹û
ID GROUP_NAME
1  aa,bb,cc
2  dd,ee
3  ff