--建表
create table tb(id int, value varchar(10)) ;
--数据
insert into tb values(1, 'aa') ;
insert into tb values(1, 'bb') ;
insert into tb values(2, 'aaa') ;
insert into tb values(2, 'bbb') ;
insert into tb values(2, 'ccc') ;--实现函数
create or replace function test(iId number) return varchar2
is r_str varchar2(1000);
v_temp varchar2(1000);
type nm_cursor is ref cursor;
v_tempCur nm_cursor;
begin
open v_tempCur for select distinct(value) from tb where id=iId;
loop
fetch v_tempCur into v_temp;
exit when v_tempCur%notfound;
r_str := r_str ||','|| v_temp;
end loop;
close v_tempCur;
r_str := substr(r_str,2,length(r_str));
return r_str;
end;-- 执行
select id ,test(id) from tb group by id ;
--结果 ID TEST(ID)
--------------------------------------- --------------------------------------------------------------------------------
1 aa,bb
2 aaa,bbb,ccc
现在几条数据的查询这样实现还可以,稍微一多就 慢了很多,请帮忙 下看还有什么好的方法没,先谢过了 ●▽●!
如果不是,请在id上建个索引。
select id
,wm_concat(value) new_value
from tb
group by a
--我靠,你这个还用做个函数吗?这也太麻烦了吧?
--直接用sql实现阿select id , wm_concat(distinct(value)) from tb group by id
with tb as (
select 1 id, 'a' value from dual union all
select 1 id, 'b' value from dual union all
select 2 id, 'c' value from dual union all
select 2 id, 'c' value from dual union all
select 2 id, 'd' value from dual
)
select id , wm_concat(distinct(value)) from tb group by id
-------------------------------------------------------------
1 a,b
2 c,d
--如果是10g或者以上的版本可以用
select id,wm_concat(value) c1 from tb group by id ID C1
---------- --------------------
1 aa,bb
2 aaa,ccc,bbb--通用版本 1 select id,max(ltrim(sys_connect_by_path(value,','),',')) c1
2 from (
3 select id,value,row_number() over(partition by id order by value) rn
4 from tb)
5 start with rn=1
6 connect by prior rn=rn-1 and prior id=id
7 group by id
8* order by id
SQL> / ID C1
---------- --------------------
1 aa,bb
2 aaa,bbb,ccc