oracle中同组内字符串连接

一次去面试，遇到了这样一道题：

如下虚拟业务环境，请根据要求编写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就不提供了。

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