[原创]oracle中同组内字符串连接_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 3416 | 回复: 0   主题: [原创]oracle中同组内字符串连接        下一篇 
chong.zhang
注册用户
等级:上尉
经验:627
发帖:25
精华:0
注册:1970-1-1
状态:离线
发送短消息息给chong.zhang 加好友    发送短消息息给chong.zhang 发消息
发表于: IP:您无权察看 2014-4-30 17:47:08 | [全部帖] [楼主帖] 楼主

一次去面试,遇到了这样一道题:

如下虚拟业务环境,请根据要求编写SQL脚本

表一:T_USER

北京联动北方科技有限公司

表二:T_ROLE

北京联动北方科技有限公司

表三:

北京联动北方科技有限公司

请用SQL语句将表一,表二内的数据填写入表三,规则:表一与表二关联字段ID,表三info字段为表一表二使用分隔符(:和@)合并而来。

这题我当时花了10分钟依然没有想出来一个好的方案来,最后不得不放弃。

其实这题最麻烦的地方是在如何将表二中同一人的权限信息合并为一行,如果没有特殊办法,应该是不好解决的,最后在网上搜索了几天,终于找到了一个oracle的函数SYS_CONNECT_BY_PATH,此函数可以把一个父节点下的所有子节点的字符串连接起来。有了这个函数,这道题就有了突破口了。

首先我们将表二中的角色(role)和权限(permission)字符连接起来,这个直接用“||”就可以做到了。

然后用SYS_CONNECT_BY_PATH 进行字符连接,参考sql

SELECT ID, LTRIM(MAX(SYS_CONNECT_BY_PATH(INFO, '@')), '@') INFO
FROM (SELECT INFO,
ID,
ROW_NUMBER() OVER(PARTITION BY ID ORDER BY INFO DESC) RN
FROM (SELECT ID AS ID, ROLE || ':' || PERMISSION AS INFO
FROM T_ROLE) A)
START WITH RN = 1
CONNECT BY RN - 1 = PRIOR RN
AND ID = PRIOR ID
GROUP BY ID


注意连接出来的字符串前面会带一个连接字符,本例中是一个@符,用ltrim或substr去掉就好了。

之后的sql就很简单了,一个left join 就解决了,sql就不提供了。

不知道这种需求在实际应用中有没有价值,反正我是碰到过,权且当作扩宽知识面吧。




赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论