Oracle如何将同用户ID的部门流转记录拼接为连续链式路径字段

前提说明 #

原始表为用户部门流转记录表，假设表名为user_dept_transfer，字段如下：

• userid：用户ID
• department_from：调出部门
• department_to：调入部门

要实现的效果为按userid分组，将连贯的流转节点用→拼接为完整流转路径。

方案1：Oracle 11gR2及以上版本（推荐，写法简洁） #

用Oracle内置的字符串聚合函数LISTAGG实现，代码如下：

SELECT 
    userid,
    LISTAGG(department_from, '→') WITHIN GROUP (ORDER BY LEVEL) 
    || '→' || MAX(department_to) AS department_trans
FROM user_dept_transfer
START WITH NOT EXISTS (
    SELECT 1 FROM user_dept_transfer t2 
    WHERE t2.userid = user_dept_transfer.userid 
    AND t2.department_to = user_dept_transfer.department_from
)
CONNECT BY PRIOR userid = userid 
AND PRIOR department_to = department_from
GROUP BY userid;

逻辑说明： #

• 先用START WITH定位每个用户的流转起点（即没有其他流转记录的调入部门等于当前记录的调出部门的记录）
• 用CONNECT BY做层级关联，把同一个用户的连贯流转记录按顺序串起来
• 用LISTAGG按流转顺序拼接所有调出部门，最后拼接上最后一条记录的调入部门，即可得到完整路径

方案2：Oracle 11gR2以下低版本兼容写法 #

用SYS_CONNECT_BY_PATH实现字符串拼接，代码如下：

SELECT userid, SUBSTR(department_trans, 2) AS department_trans
FROM (
    SELECT 
        userid,
        SYS_CONNECT_BY_PATH(department_from, '→') || '→' || department_to AS department_trans,
        CONNECT_BY_ISLEAF AS is_leaf
    FROM user_dept_transfer
    START WITH NOT EXISTS (
        SELECT 1 FROM user_dept_transfer t2 
        WHERE t2.userid = user_dept_transfer.userid 
        AND t2.department_to = user_dept_transfer.department_from
    )
    CONNECT BY PRIOR userid = userid 
    AND PRIOR department_to = department_from
)
WHERE is_leaf = 1;

逻辑说明： #

• 层级关联逻辑和方案1完全一致
• 用SYS_CONNECT_BY_PATH按层级拼接部门，CONNECT_BY_ISLEAF筛选出每个用户的最后一条流转记录对应的完整路径
• 用SUBSTR去掉路径开头多余的→符号

以上两种写法都可直接得到需求的输出结果，可根据自身使用的Oracle版本选择。

内容的提问来源于stack exchange，提问作者Bing Yu