在 Oracle 层级查询中提取省级单位编码的技巧

在处理树形结构数据（如单位表、行政区划表）时，常常需要在每条记录上附加它所属的上级或特定层级的编码。本文将围绕 Oracle 的 CONNECT BY 和 SYS_CONNECT_BY_PATH 函数，讲解如何提取“省级单位”的 dept_code，并附加到每条记录上。

业务场景

假设我们有一个单位表 mag_or_supply_dept，结构如下：

| id | dept\_code | dept\_name | parent\_id | dept\_type |
| -- | ---------- | ---------- | ---------- | ---------- |
| 1  | 1000       | 国网         | null       | 1          |
| 2  | 1100       | 北京省        | 1          | 2          |
| 3  | 1101       | 北京市        | 2          | 3          |
| 4  | 110101     | 海淀区        | 3          | 4          |

目标：在每条记录上加一列 province_code，表示所属省编码。

• 省自己 → 显示自己的编码
• 市、县 → 显示所属省的编码
• 国网（顶级） → 可以忽略或显示 NULL

SQL 实现

SELECT d.id,
       d.dept_code,
       d.dept_name,
       d.parent_id,
       d.dept_type,
       CASE 
         WHEN LEVEL >= 2 
         THEN REGEXP_SUBSTR(
                LTRIM(SYS_CONNECT_BY_PATH(d.dept_code, '/'), '/'),  -- 去掉开头的 '/'
                '[^/]+', 1, 2)                                      -- 抽第2段
         ELSE NULL
       END AS province_code
FROM mag_or_supply_dept d
START WITH dept_name = '国网'
CONNECT BY PRIOR id = parent_id;

关键函数解析

1. LEVEL

• Oracle 内置伪列，用于表示当前节点在层级树中的深度。
• LEVEL = 1 → 根节点（本例中是国网）
• LEVEL = 2 → 省
• LEVEL = 3 → 市
• LEVEL = 4 → 县

2. SYS_CONNECT_BY_PATH(column, '/')

• 功能：返回从根节点到当前节点的路径，路径中的每个节点用指定分隔符 / 连接。

• 示例：

  SYS_CONNECT_BY_PATH(d.dept_code, '/')
  

  对“海淀区”返回 /1000/1100/110101

3. LTRIM(..., '/')

• 去掉路径字符串开头多余的 /
• /1000/1100/110101 → 1000/1100/110101

4. REGEXP_SUBSTR(string, pattern, start_position, occurrence)

• 用于正则匹配子串

• 参数解析：

  1. string → 要匹配的源字符串（本例中是 1000/1100/110101）

  2. pattern → 正则表达式 '[^/]+' 表示匹配非 / 的连续字符

  3. start_position → 从第 1 个字符开始匹配

  4. occurrence → 第几次匹配

     • 1 → 第 1 段（国网）
     • 2 → 第 2 段（省）
     • 3 → 第 3 段（市/县）

• 本例：

  REGEXP_SUBSTR(LTRIM(SYS_CONNECT_BY_PATH(d.dept_code, '/'), '/'), '[^/]+', 1, 2)
  

  → 提取路径中的第 2 段，也就是省的 dept_code

5. CASE WHEN LEVEL >= 2 ... ELSE NULL END

• 为了忽略根节点（国网），只在 省及以下节点 执行提取
• LEVEL < 2 → 返回 NULL

查询结果示例

| id | dept\_code | dept\_name | parent\_id | dept\_type | province\_code |
| -- | ---------- | ---------- | ---------- | ---------- | -------------- |
| 2  | 1100       | 北京省        | 1          | 2          | 1100           |
| 3  | 1101       | 北京市        | 2          | 3          | 1100           |
| 4  | 110101     | 海淀区        | 3          | 4          | 1100           |

可以看到，无论是省、市、县，province_code 都正确显示所属省的编码。

总结

1. SYS_CONNECT_BY_PATH + REGEXP_SUBSTR 是 Oracle 层级查询中提取路径中某一层级字段的经典做法。
2. LEVEL 用于判断节点层级，配合 CASE 可以控制哪些节点需要提取省编码。
3. 该方法适用于单位、行政区划等树形数据统计场景，尤其方便做按省分组统计。