不用自定义函数如何实现这两张表的关联
有两张表
表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中是否能实现，请专业人士赐教！
[解决办法]
另写了一个，我测试是成功的，你试试看~~~
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
