Oracle中某个字段里的值为逗号隔开的多个值

最近遇到很变态的问题，某字段与另一表有字段关联关系，单位这字段存放的值是多个以逗号隔开的ID，改如何处理的问题，网上搜了下遇到此问题的人还挺不少，给一网友的问题整理了一下，转过来学习一下：

表a：
a_usr_id(字符串型，关联表b的usr_id)
123,124,125
表b：
usr_id ,   usr_name,
123        name1
124        name2
125        name3

直接用用sql语句，不用存储过程如何取得这样的数据集合：

name1,name2,name3

解决方案：

SQL> select * from test;
A_URS_ID
--------------------------------------------------
123,125
123,124,125

SQL> select * from test1;

URS_ID     URS_NAME
---------- --------------------
123        name1
124        name2
125        name3

SQL> with split_data as (
         select key, rn, substr(str, instr(str, ',', 1, rn)+1,
                                instr(str, ',', 1, rn+1) - instr(str, ',', 1, rn) - 1) str
            from (select a_urs_id key, ','||a_urs_id||',' str from test) a,
                (select rownum rn from dual connect by rownum < 10) b
         where instr(str, ',', 1, rn+1) > 0 )
     select key, substr(max(sys_connect_by_path(urs_name, ',')), 2) a_name
         from split_data a, test1 b
         where a.str = b.urs_id

       start with rn = 1
      connect by key = prior key and rn-1 = prior rn
      group by key;

KEY                  A_NAME
-------------------- ------------------------------------------------------------
123,124,125          name1,name2,name3
123,125               name1,name3



附原文地址：  http://www.oracle.com.cn/viewthread.php?tid=75324

另参考：  http://www.233.com/oracle/zonghe/20110704/161631965.html