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

2007-08-17 05:50:01 206 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

1978ssssss 墨衍会员 · AI 创作全网分发关注 + 加入社区

IBM Websphere MQ 基础0：Linux下安装IBM MQ 7.5 背景无。公司就是用的IBM WebSphere MQ作为消息中间件，本文记载how install it. 一. 安装包检查与准备 1. 检查通过rpm –qa检查是否已经安装 IBM MQ，若已安装，就不必再安装了（或用卸载rpm –e XXX重装）： rpm -qa | grep MQ 2. 准备如下安装包： WS_MQ_LINUX_ON... 阅读详情

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

...全文

Linux安装消息队列IBM MQ 7.5开发版安装配置详解消息队列IBM MQ 7.5开发版安装配置详解文章目录消息队列IBM MQ 7.5开发版安装配置详解前言一、什么是IBM MQ？二、安装前准备1、安装前准备2、安装MQ Server3、启动实例4、创建队列一个名为TEXT的队列(测试)5、启动端口监听6、启动成功总结前言 IBM 已经熟练地优化了 MQ（消息队列），在市场上已有超过 25 年的历史。借助 MQ（消息队列），如果无法立即传递消息，那么会在其等待队列中确保消息安全，直到保证传递为止。竞争产品可能会传递两次消息或根本不传递消息，而 M 阅读详情

1978ssssss

Oracle 17,133 成员 55,258 内容

+ 关注 + 加入社区

切换为时间正序

1978ssssss 2007-08-18

打赏
举报

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

1978ssssss 2007-08-18

打赏
举报

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

jdsnhan 2007-08-18

打赏
举报

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

mantisXF 2007-08-18

打赏
举报

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

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

mantisXF 2007-08-17

打赏
举报

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

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

1978ssssss 2007-08-17

打赏
举报

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

mantisXF 2007-08-17

打赏
举报

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

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

相关推荐