求助：Oracle数据库实现分支、部门、科室层级树形查询

我们需要基于三个Oracle数据库表构建树形查询，展示分支、部门（含父子层级）及对应科室的关联数据：

• Branch：存储分支信息
• DEPT：存储部门信息，通过ParentID关联DEPT_ID形成父子层级，根节点的ParentID为null
• SECTS：存储科室信息，通过DEPT_ID关联所属部门

表结构与测试数据 #

1. Branch表建表语句 #

CREATE TABLE Branch
(
  BRANCH_ID            NUMBER                   NOT NULL,
  BRANCH_NAME          VARCHAR2(100 BYTE)       NOT NULL,
  START_DATE           DATE,
  END_DATE             DATE, 
  CREATED_BY           VARCHAR2(90 BYTE),
  CREATION_DATE        DATE
)

2. DEPT表建表语句 #

CREATE TABLE DEPT
(
  DEPT_ID          NUMBER,
  PARENTID             NUMBER,
  DEPT_NAME            VARCHAR2(200 BYTE),
  STATUS               NUMBER,
  BRANCH_ID            NUMBER,
  DESCRIPTION          VARCHAR2(100 BYTE),
  CREATED_BY           VARCHAR2(90 BYTE),
  CREATION_DATE        DATE
)

3. SECTS表建表语句 #

CREATE TABLE SECTS
(
  INT_SECT_ID          NUMBER,
  DEPT_ID              NUMBER, 
  SECT_NAME            VARCHAR2(200 BYTE), 
  DESCRIPTION          VARCHAR2(100 BYTE),
  STATUS               NUMBER,
  CREATED_BY           VARCHAR2(90 BYTE),
  CREATION_DATE        DATE 
)

测试数据插入语句 #

Branch表

INSERT INTO Branch (BRANCH_ID,  BRANCH_NAME, CREATION_DATE)
VALUES (1,'India',sysdate);

DEPT表

INSERT INTO DEPT (DEPT_ID, PARENTID, DEPT_NAME,BRANCH_ID)
VALUES (1,null,'HDM Department',1);

INSERT INTO DEPT (DEPT_ID, PARENTID, DEPT_NAME,BRANCH_ID)
VALUES (2,1,'IT department',1);

INSERT INTO DEPT (DEPT_ID, PARENTID, DEPT_NAME,BRANCH_ID)
VALUES (3,2,'Technial Department',1);

SECTS表

INSERT INTO SECTS (INT_SECT_ID, DEPT_ID,SECT_NAME,CREATION_DATE)
VALUES (1,1,'Projects Managers Section',sysdate); 

INSERT INTO SECTS (INT_SECT_ID, DEPT_ID,SECT_NAME,CREATION_DATE)
VALUES (1,2,'Software Section',sysdate); 

INSERT INTO SECTS (INT_SECT_ID, DEPT_ID,SECT_NAME,CREATION_DATE)
VALUES (2,2,'Network Section',sysdate); 

解决方案：树形查询SQL语句 #

以下SQL使用Oracle的CONNECT BY语法处理部门的层级关系，同时关联分支表和科室表，确保所有部门（包括无对应科室的部门）都能显示：

SELECT
    b.BRANCH_NAME,
    -- 用LPAD缩进展示部门层级结构
    LPAD(' ', (LEVEL - 1) * 4) || d.DEPT_NAME AS DEPT_HIERARCHY,
    s.SECT_NAME
FROM
    DEPT d
    JOIN Branch b ON d.BRANCH_ID = b.BRANCH_ID
    LEFT JOIN SECTS s ON d.DEPT_ID = s.DEPT_ID
START WITH
    d.PARENTID IS NULL
CONNECT BY PRIOR
    d.DEPT_ID = d.PARENTID
ORDER SIBLINGS BY
    d.DEPT_NAME;

语句说明 #

1. START WITH d.PARENTID IS NULL：指定部门树的根节点（即顶级部门）
2. CONNECT BY PRIOR d.DEPT_ID = d.PARENTID：定义部门层级的关联规则，父部门的DEPT_ID等于子部门的PARENTID
3. LPAD(' ', (LEVEL - 1) * 4) || d.DEPT_NAME：通过LEVEL函数获取当前部门的层级，用LPAD添加缩进，直观展示树形结构
4. LEFT JOIN SECTS s：确保即使部门没有对应科室，该部门信息也会被列出（此时SECT_NAME为null）
5. ORDER SIBLINGS BY d.DEPT_NAME：按部门名称排序同级部门，保持树形结构的有序性

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