如何在DB2中实现Oracle式员工层级及薪资累积统计？

需求背景 #

我们基于scott.emp表做层级查询，原始查询语句为：

select empno, ename,mgr, sal from emp order by empno ;

原始数据表 #

期望输出结果 #

需要展示员工层级（用.缩进区分），以及该员工及其所有下属的薪资累积总额：

Oracle实现回顾 #

原Oracle通过CONNECT BY语法结合CTE实现需求：

WITH pohon AS (
    SELECT DISTINCT CONNECT_BY_ROOT empno parent_id, empno AS id 
    FROM emp 
    CONNECT BY PRIOR empno = mgr
), trx AS (
    SELECT pohon.parent_id, SUM (tx.sal) AS amount 
    FROM pohon JOIN emp tx ON pohon.id = tx.empno 
    GROUP BY pohon.parent_id
) 
SELECT 
    LPAD (r0.ename, LENGTH (r0.ename) + LEVEL * 1 - 1, '.') AS deskripsi,
    empno, 
    mgr, 
    trx.amount 
FROM emp r0 
JOIN trx ON r0.empno = trx.parent_id 
START WITH r0.mgr IS NULL 
CONNECT BY r0.mgr = PRIOR r0.empno ;

DB2解决方案 #

DB2使用**递归公共表达式（WITH RECURSIVE）**来实现层级查询，我们可以用两种方式实现需求：

方案1：基于路径匹配的薪资汇总 #

适合数据量不大的场景，逻辑和Oracle实现更贴近：

WITH RECURSIVE emp_hierarchy (empno, ename, mgr, level, path) AS (
    -- 锚点成员：定位根节点（KING，无上级）
    SELECT 
        empno, 
        ename, 
        mgr, 
        1 AS level,
        CAST(ename AS VARCHAR(100)) AS path
    FROM emp
    WHERE mgr IS NULL
    
    UNION ALL
    
    -- 递归成员：遍历所有下属节点，记录层级和路径
    SELECT 
        e.empno, 
        e.ename, 
        e.mgr, 
        eh.level + 1 AS level,
        CAST(eh.path || '.' || e.ename AS VARCHAR(100)) AS path
    FROM emp e
    JOIN emp_hierarchy eh ON e.mgr = eh.empno
),
salary_rollup AS (
    -- 计算每个节点及其所有下属的薪资总和
    SELECT 
        eh.empno AS parent_id,
        SUM(e.sal) AS amount
    FROM emp_hierarchy eh
    JOIN emp e ON e.empno IN (
        SELECT empno FROM emp_hierarchy WHERE path LIKE eh.path || '%'
    )
    GROUP BY eh.empno
)
-- 最终生成带缩进的层级展示并关联薪资数据
SELECT 
    LPAD('', level - 1, '.') || ename AS deskripsi,
    eh.empno,
    eh.mgr,
    sr.amount
FROM emp_hierarchy eh
JOIN salary_rollup sr ON eh.empno = sr.parent_id
ORDER BY path;

方案2：递归累加薪资（性能更优） #

避免了路径匹配的开销，适合大表场景：

WITH RECURSIVE emp_hierarchy (root_empno, empno, sal) AS (
    -- 锚点成员：每个员工自身作为初始根节点
    SELECT empno, empno, sal FROM emp
    
    UNION ALL
    
    -- 递归成员：向上关联父节点，累加薪资
    SELECT eh.root_empno, e.empno, eh.sal + e.sal
    FROM emp_hierarchy eh
    JOIN emp e ON eh.empno = e.mgr
),
salary_rollup AS (
    -- 聚合每个根节点的总薪资
    SELECT root_empno AS parent_id, SUM(sal) AS amount
    FROM emp_hierarchy
    GROUP BY root_empno
),
emp_levels AS (
    -- 生成员工的层级信息
    WITH RECURSIVE levels (empno, ename, mgr, level) AS (
        SELECT empno, ename, mgr, 1 AS level FROM emp WHERE mgr IS NULL
        UNION ALL
        SELECT e.empno, e.ename, e.mgr, l.level + 1 AS level
        FROM emp e JOIN levels l ON e.mgr = l.empno
    )
    SELECT * FROM levels
)
-- 组合层级展示和薪资数据
SELECT 
    LPAD('', level - 1, '.') || ename AS deskripsi,
    el.empno,
    el.mgr,
    sr.amount
FROM emp_levels el
JOIN salary_rollup sr ON el.empno = sr.parent_id
ORDER BY 
    CASE WHEN mgr IS NULL THEN 0 ELSE mgr END, empno;

两种方案都能输出符合要求的结果，你可以根据实际数据规模选择合适的版本。

内容的提问来源于stack exchange，提问作者Durian Parongil