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

[1978ssssss]

有两张表
表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中是否能实现，请专业人士赐教！

[mantisXF]

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

[1978ssssss]

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

[mantisXF]

我都出数据了，还不相信我啊~~ 晕

自己在oracle里测试了再说吧...

[mantisXF]

另写了一个，我测试是成功的，你试试看~~~

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

[jdsnhan]

除非内容固定，否则不用函数很难实现

[1978ssssss]

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

[1978ssssss]

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应该是编号(如上),再帮帮忙吧!