ORACLE查询问题，高手帮忙！

Question

高手有时间帮我看看吧，在这里先多谢了！测试数据如下：
CREATE TABLE CESHI
(
  PRIMARY_ID    NUMBER NOT NULL,
  USERID        NUMBER(6),
  USERNAME VARCHAR2(32),
  ANOTHER_NAME VARCHAR2(400)
);
 
INSERT INTO CESHI(PRIMARY_ID,USERID,USERNAME,ANOTHER_NAME) VALUES (1,1001,'张三','小张');
INSERT INTO CESHI(PRIMARY_ID,USERID,USERNAME,ANOTHER_NAME) VALUES (2,1001,'张三','张老板');
INSERT INTO CESHI(PRIMARY_ID,USERID,USERNAME,ANOTHER_NAME) VALUES (3,1001,'张三','张总');
INSERT INTO CESHI(PRIMARY_ID,USERID,USERNAME,ANOTHER_NAME) VALUES (4,1001,'张三','张先生');
INSERT INTO CESHI(PRIMARY_ID,USERID,USERNAME,ANOTHER_NAME) VALUES (5,1002,'李四','小李');
INSERT INTO CESHI(PRIMARY_ID,USERID,USERNAME,ANOTHER_NAME) VALUES (6,1002,'李四','李老板');
INSERT INTO CESHI(PRIMARY_ID,USERID,USERNAME,ANOTHER_NAME) VALUES (7,1002,'李四','李先生');
INSERT INTO CESHI(PRIMARY_ID,USERID,USERNAME,ANOTHER_NAME) VALUES (8,1003,'王五','小王');
INSERT INTO CESHI(PRIMARY_ID,USERID,USERNAME,ANOTHER_NAME) VALUES (9,1003,'王五','王哥');我想得到的数据如下：USERID    USERNAME   MAT_NAME
1001      张三       小张；张老板；张总；张先生  
1002      李四       小李；李老板；李先生
1003      王五       小王；王哥我自己写的语句比较粗糙，我先建一张专门存储名字和ID的表ceshi_01，插入ceshi表中去重复的id,name；
然后循环执行下面的更新语句：UPDATE CESHI_01 C1
   SET C1.MAT_TEXT = C1.MAT_TEXT || ';' ||
                          (SELECT C.ANOTHER_NAME
                             FROM CESHI C
                            WHERE C.USERID = C1.USERID
                              AND INSTR(';' || C1.MAT_TEXT || ';',
                                        ';' ||
                                        TO_SINGLE_BYTE(TRIM(C.ANOTHER_NAME)) || ';',
                                        1) = 0
                              AND ROWNUM = 1)测试数据是可以实现我要的效果，但我实际执行的数据量大概有40万条左右，去重复后光名字ID就有8W条。我按照上面方式去更新的时候，ORACLE执行了几个小时都没有完成；请高手帮我优化下，或者指点一个更加方便的方法。再次谢过！

Answer 1

--10g或以上版本支持wm_concat()
SQL> select userid,username,wm_concat(another_name)
  2  from ceshi group by userid,username;    USERID USERNAME
---------- --------------------------------
WM_CONCAT(ANOTHER_NAME)
-----------------------------------------------------------------
      1001 张三
小张,张老板,张总,张先生      1002 李四
小李,李老板,李先生      1003 王五
小王,王哥
ame

Answer 2

SQL> select USERID,USERNAME,
  2  replace(wm_concat(ANOTHER_NAME),',',';') ANOTHER_NAME
  3  from CESHI
  4  group by USERID,USERNAME
  5  /    USERID USERNAME                         ANOTHER_NAME
---------- -------------------------------- ---------------------------------
      1001 张三                             小张;张老板;张总;张先生
      1002 李四                             小李;李老板;李先生
      1003 王五                             小王;王哥

Answer 3

CREATE TABLE CESHI
(
PRIMARY_ID NUMBER NOT NULL,
USERID NUMBER(6),
USERNAME VARCHAR2(32),
ANOTHER_NAME VARCHAR2(400)
);INSERT INTO CESHI(PRIMARY_ID,USERID,USERNAME,ANOTHER_NAME) VALUES (1,1001,'张三','小张');
INSERT INTO CESHI(PRIMARY_ID,USERID,USERNAME,ANOTHER_NAME) VALUES (2,1001,'张三','张老板');
INSERT INTO CESHI(PRIMARY_ID,USERID,USERNAME,ANOTHER_NAME) VALUES (3,1001,'张三','张总');
INSERT INTO CESHI(PRIMARY_ID,USERID,USERNAME,ANOTHER_NAME) VALUES (4,1001,'张三','张先生');
INSERT INTO CESHI(PRIMARY_ID,USERID,USERNAME,ANOTHER_NAME) VALUES (5,1002,'李四','小李');
INSERT INTO CESHI(PRIMARY_ID,USERID,USERNAME,ANOTHER_NAME) VALUES (6,1002,'李四','李老板');
INSERT INTO CESHI(PRIMARY_ID,USERID,USERNAME,ANOTHER_NAME) VALUES (7,1002,'李四','李先生');
INSERT INTO CESHI(PRIMARY_ID,USERID,USERNAME,ANOTHER_NAME) VALUES (8,1003,'王五','小王');
INSERT INTO CESHI(PRIMARY_ID,USERID,USERNAME,ANOTHER_NAME) VALUES (9,1003,'王五','王哥');select userid,username,wm_concat(another_name)
from ceshi
group by userid,username

Answer 4

--针对你这个需求，有三种方法可以实现
--方法一、使用wmsys.wm_concat  Oracle版本要求10g及以上
--方法二、使用sys_connect_by_path  Oracle版本在9i及以下
--方法三、使用自定义函数 --我来给你写给自定义函数实现的例子--创建自定义函数
create or replace function my_concat(p_userid in ceshi.userid%type)  --输入参数：p_userid
return varchar2          
is  
result varchar2(4000);    --定义变量，返回值  
begin  
       for temp_cursor in (select another_name from ceshi where userid=p_userid) loop     --此处在游标FOR循环中查询userid对应的another_name   
           result :=result||temp_cursor.another_name||';';           
       end loop;   
       result := rtrim(result,';');  --去掉最后一个分号 
       return result;   
end;  
/
 
SELECT DISTINCT userid,username,my_concat(userid) another_name FROM ceshi ;结果:
    USERID   USERNAME           ANOTHER_NAME
------------ ------------------ ---------------------------------
      1001   张三                  小张;张老板;张总;张先生
       1002   李四                  小李;李老板;李先生
       1003   王五                  小王;王哥

Answer 5

首先：非常感谢楼上各位的回答！
这个函数非常好用，一下子就解决了我绕了半天的问题。
但我在执行我自身数据的时候遇到另外一个问题，刚开始查询，wm_concat(another_name)出现乱码。我改成
wm_concat(to_char(another_name))后解决了乱码问题，但如果wm_concat(to_char(another_name))结果非常大，比如超过了1W个字节。就会提示字符串缓冲区太小的错误。我用wm_concat(to_clob(to_char(another_name)))后问题依旧，请问：这个该如何解决。

Answer 6

楼上说错，to_clob(wm_concat(to_char(another_name)))后也是提示缓冲区太小。