求解一个SQL解析字符串的问题

Question

有两张表：
一张表TABLE1
ID     work_no    name
----------------------
1       1000      张三
2       1001      李四
3       1002      王五
另一张表TABLE2
ID     WORK_NO
--------------
1       1000,1001
2       1000,1002
3       1001,1002
查询TABLE2的时候希望得到如下结果
1       1000,1001    张三,李四
2       1000,1002    张三,王五
3       1001,1002    李四,王五
想写一个函数来解析TABLE2中的WORK_NO,不知道如何写，请哪位高手指点一下，谢谢
       

Answer 1

用正则表达式
with table1 as
(select 1 id,'1000' work_no ,'张三' name from dual union all
select 2 id,'1001' work_no ,'李四' name from dual union all
select 3 id,'1002' work_no ,'王五' name from dual 
),
table2 as
(select 1 id,'1000,1001' work_no  from dual union all
select 2 id,'1000,1002' work_no  from dual union all
select 3 id,'1001,1002' work_no  from dual 
)
select a.*
        ,b.name||','||c.name
from table2 a
left join table1 b on regexp_substr(a.work_no,'[^\,]+',1,1)=b.work_no
left join table1 c on regexp_substr(a.work_no,'[^\,]+',1,2)=c.work_no
;        ID WORK_NO   B.NAME||','||C.NAME
---------- --------- -------------------
         1 1000,1001 张三,李四
         3 1001,1002 李四,王五
         2 1000,1002 张三,王五

Answer 2

   SELECT   LEVEL,
             REGEXP_SUBSTR (
                'first,second,third',
                '[^,]*',
                NVL (REGEXP_INSTR ('first,second,third',
                                   ',',
                                   1,
                                   DECODE (LEVEL - 1, 0, NULL, LEVEL - 1)), 0)
                + 1
             )
                split_result
      FROM   DUAL
CONNECT BY   NVL (REGEXP_INSTR ('first,second,third',
                                ',',
                                1,
                                DECODE (LEVEL - 1, 0, NULL, LEVEL - 1)), 1) >
                0

Answer 3

select table2.id,table2.work_no,wm_concat(table1.name)
from table2 
left join table1 
on instr(table2.work_no,table1.work_no) > 0
group by table2.id,table2.work_no

Answer 4

借用下SQL,这样比较严谨
select table2.id,table2.work_no,wm_concat(table1.name)
from table2 
left join table1 
on instr(','||table2.work_no||',',','||table1.work_no||',') > 0
group by table2.id,table2.work_no

Answer 5

lz的Oracle是什么版本,函数wm_concat要10g或以上才有,不然要自己写

Answer 6

用 instr 不太合适
考虑一下这种情况
  100 张三
  1000 李四
  1001 王五
 有一条记录 1000，1001 ，进行关联后 【100 张三】 这条数据也会关联上了，bug啊
    

Answer 7

带上分隔符做instr就可以了，这个问题6楼已经解决了。

Answer 8

给你段SQL，可以自已建立一个类似与wm_concat的函数，函数名是F_LINK
-- 建立Type
CREATE OR REPLACE TYPE "T_LINK"                                                                                                                                                   AS OBJECT ( STR VARCHAR2(30000), STATIC FUNCTION ODCIAGGREGATEINITIALIZE (SCTX IN OUT T_LINK) RETURN NUMBER, MEMBER FUNCTION ODCIAGGREGATEITERATE (SELF IN OUT T_LINK, VALUE IN VARCHAR2) RETURN NUMBER, MEMBER FUNCTION ODCIAGGREGATETERMINATE (SELF IN T_LINK, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER, MEMBER FUNCTION ODCIAGGREGATEMERGE (SELF IN OUT T_LINK, CTX2 IN T_LINK) RETURN NUMBER);-- 建立type body
CREATE OR REPLACE TYPE BODY T_LINK IS  STATIC FUNCTION ODCIAGGREGATEINITIALIZE (SCTX IN OUT T_LINK) RETURN NUMBER IS  BEGIN   SCTX := T_LINK(NULL);   RETURN ODCICONST.SUCCESS;  END;  MEMBER FUNCTION ODCIAGGREGATEITERATE (SELF IN OUT T_LINK, VALUE IN VARCHAR2) RETURN NUMBER IS  BEGIN   SELF.STR := SELF.STR || VALUE;   RETURN ODCICONST.SUCCESS;  END;  MEMBER FUNCTION ODCIAGGREGATETERMINATE (SELF IN T_LINK, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER IS  BEGIN   RETURNVALUE := SELF.STR;   RETURN ODCICONST.SUCCESS;  END;  MEMBER FUNCTION ODCIAGGREGATEMERGE (SELF IN OUT T_LINK, CTX2 IN T_LINK) RETURN NUMBER IS  BEGIN   NULL;   RETURN ODCICONST.SUCCESS;  END;END;-- 建立函数
CREATE OR REPLACE FUNCTION F_LINK(P_STR VARCHAR2) RETURN VARCHAR2 AGGREGATE USING T_LINK;