基于Oracle CONNECT BY子句的部门费用汇总SQL查询需求

嘿，这就帮你搞定这个部门层级费用统计的需求！结合你提供的EXPENSE和PARENT_CHILD两张表的数据，我们可以用Oracle的CONNECT BY子句实现一个能展示部门层级结构，同时汇总每个部门及其所有下属部门总费用的查询。

先明确下你的表数据（整理成表格更清晰） #

EXPENSE表

PARENT_CHILD表

解决方案SQL #

SELECT
    -- 标记当前部门的层级深度，根节点DEPT_G为层级1
    LEVEL AS dept_level,
    -- 用缩进展示层级关系，让结构更直观
    RPAD(' ', (LEVEL - 1) * 4) || d.dept AS dept_name,
    -- 汇总当前部门及其所有子部门的总费用
    (SELECT SUM(e.expense_amt)
     FROM expense e
     JOIN parent_child pc ON e.dept = pc.child_dept
     START WITH pc.child_dept = d.dept
     CONNECT BY PRIOR pc.parent_dept = pc.child_dept) AS total_expense
FROM (
    -- 收集所有部门：包括费用表中的部门和层级表中的父部门，确保DEPT_F、DEPT_G被纳入
    SELECT DISTINCT dept FROM expense
    UNION
    SELECT DISTINCT parent_dept FROM parent_child
) d
START WITH d.dept = 'DEPT_G' -- 从最高层级的根部门开始递归遍历
CONNECT BY PRIOR d.dept = (SELECT parent_dept FROM parent_child WHERE child_dept = d.dept)
ORDER BY dept_level, dept_name;

关键逻辑说明 #

• 子查询d：先把所有存在的部门都收集起来，避免漏掉没有直接费用的部门（比如DEPT_F、DEPT_G）。
• START WITH + CONNECT BY：从根部门DEPT_G开始，按照PARENT_CHILD表的层级关系向下递归遍历所有子部门，LEVEL字段会自动标记每个部门的层级深度。
• 费用汇总子查询：对于每个部门，再次用CONNECT BY反向递归，遍历它的所有子部门，把这些子部门的费用加总起来，得到该部门的总管控费用。
• 缩进格式化：用RPAD函数给不同层级的部门添加空格缩进，让层级结构一目了然。

预期输出示例 #

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