不用自定义函数如何实现这两张表的关联

Question

有两张表
表1:usertab
-----------------------------------------
userid    username
01         aaa
02         bbb
03         ccc表2:worktab
-----------------------------------------
workid workname userid
1        1       01
2        2       01,02,03
3        3       03,01如何得到以下结果集
-----------------------------------------
workid workname username
1        1       aaa
2        2       aaa,bbb,ccc
3        3       ccc,aaa
因为是连接别人的数据库,所以不能用自定义函数、存储过程等，只能用select。不知道oracle中是否能实现，请专业人士赐教！

Answer 1

SQL>  select distinct worktab.workid,worktab.workname,replace(worktab.username,usertab.userid,usertab.username)
  2     from (select '01' as userid,'aaa' as username from dual
  3           union all
  4           select '02' as userid,'bbb' as username from dual
  5           union all
  6           select '03' as userid,'ccc' as username from dual
  7          )usertab,
  8          (select 1 as workid,'1' as workname,'aaa' as username from dual
  9           union all
 10           select 2 as workid,'2' as workname,'aaa,bbb,ccc' as username from dual
 11           union all
 12           select 3 as workid,'3' as workname,'ccc,aaa' as username from dual
 13          )worktab;    WORKID WORKNAME REPLACE(WORKTAB.USERNAME,USERT
---------- -------- ---------------------------------
         1 1        aaa
         2 2        aaa,bbb,ccc
         3 3        ccc,aaa

Answer 2

不行啊,我手头现在没有oracle,在sql server中试过了不行.

Answer 3

我都出数据了，还不相信我啊~~ 晕自己在oracle里测试了再说吧...

Answer 4

select distinct worktab.workid,worktab.workname,replace(worktab.username,usertab.userid,usertab.username)
from (select '01' as userid,'aaa' as username from dual
           union all
            select '02' as userid,'bbb' as username from dual
           union all
            select '03' as userid,'ccc' as username from dual
           )usertab,
           (select 1 as workid,'1' as workname,'01' as username from dual
            union all
            select 2 as workid,'2' as workname,'01,02,03' as username from dual
            union all
            select 3 as workid,'3' as workname,'03,01' as username from dual
           )worktab;
大哥,是你写的数据和我要求的不一样,worktab表中存的username应该是编号(如上),再帮帮忙吧!

Answer 5

另写了一个，我测试是成功的，你试试看~~~select distinct zz.workid,
         zz.workname,
         ltrim(first_value(zz.path) over(partition by zz.workid,zz.workname order by zz.lev desc),',') as name
    from (select tt.*,
                level lev,
                sys_connect_by_path(tt.name,',') as path 
           from (select distinct worktab.workid,
                                worktab.workname,
                                worktab.workid||row_number() over(partition by worktab.workid,worktab.workname order by worktab.workid,worktab.workname,instr(worktab.username||',',usertab.userid,1,rn)) -1 as left_num,
                                worktab.workid||row_number() over(partition by worktab.workid,worktab.workname order by worktab.workid,worktab.workname,instr(worktab.username||',',usertab.userid,1,rn)) as right_num,
                                instr(worktab.username||',',usertab.userid,1,rn) as num,
                                decode(sign(instr(worktab.username||',',usertab.userid,1,rn)),1,usertab.username) as name
                  from (select '01' as userid, 'aaa' as username
                          from dual
                        union all
                        select '02' as userid, 'bbb' as username
                          from dual
                        union all
                        select '03' as userid, 'ccc' as username from dual) usertab,
                       (select 1 as workid, '1' as workname, '01' as username
                          from dual
                        union all
                        select 2 as workid, '2' as workname, '01,02,03' as username
                          from dual
                        union all
                        select 3 as workid, '3' as workname, '03,01' as username from dual) worktab,
                        (
                        select rownum rn from all_objects where rownum < 10
                        )
                  where instr(worktab.username||',',usertab.userid,1,rn) > 0
                  )tt
           start with tt.left_num is not null
           connect by prior tt.right_num = tt.left_num
          )zz;
===============================result=============================
    WORKID WORKNAME NAME
---------- -------- ---------------------------------------------
         1 1        aaa
         2 2        aaa,bbb,ccc
         3 3        ccc,aaa

Answer 6

赞!高手!
oracle也挺狠,愣是没看明白,不过好像实现了,谢谢mantisXF(枫の叶) .