SQL笔记-递归/树形查询

╰+攻爆jí腚メ 2023-10-12 12:48 41阅读 0赞

### 1. 递归查询说明 ###

#### Oracle ####

-- 使用递归查询获取员工的所有下属
    WITH subordinates (employee_id, subordinate_id, level) AS (
      SELECT employee_id, subordinate_id, 1
      FROM employee
      WHERE employee_id = 1 -- 以员工ID为1为例
      UNION ALL
      SELECT e.employee_id, e.subordinate_id, s.level + 1
      FROM employee e
      JOIN subordinates s ON e.employee_id = s.subordinate_id
    )
    SELECT employee_id, subordinate_id, level
    FROM subordinates;

Oracle使用`WITH`关键字来创建递归查询，首先查询出初始条件（即以某个员工ID为起点），然后通过`UNION ALL`将其和子查询（即下属）拼接起来，直到查询出所有下属。

#### MySQL ####

-- 使用递归查询获取员工的所有下属
    WITH RECURSIVE subordinates (employee_id, subordinate_id, level) AS (
      SELECT employee_id, subordinate_id, 1
      FROM employee
      WHERE employee_id = 1 -- 以员工ID为1为例
      UNION ALL
      SELECT e.employee_id, e.subordinate_id, s.level + 1
      FROM employee e
      JOIN subordinates s ON e.employee_id = s.subordinate_id
    )
    SELECT employee_id, subordinate_id, level
    FROM subordinates;

MySQL使用`WITH RECURSIVE`关键字来创建递归查询，其语法与Oracle类似。

#### SQL Server ####

SQL Server也使用`WITH`关键字来创建递归查询，其语法与Oracle类似。

#### PostGreSQL ####

PostGreSQL也使用`WITH RECURSIVE`关键字来创建递归查询，其语法与MySQL类似。

### 2. 多表递归查询示例 ###

#### 建表语句和数据插入语句 ####

-- 创建部门表
    CREATE TABLE department (
      department_id INTEGER PRIMARY KEY,
      department_name VARCHAR(50),
      parent_department_id INTEGER
    );
    
    -- 插入部门数据
    INSERT INTO department VALUES (1, '总公司', NULL);
    INSERT INTO department VALUES (2, '财务部', 1);
    INSERT INTO department VALUES (3, '人力资源部', 1);
    INSERT INTO department VALUES (4, '财务部1', 2);
    INSERT INTO department VALUES (5, '财务部2', 2);
    INSERT INTO department VALUES (6, '人力资源部1', 3);
    INSERT INTO department VALUES (7, '人力资源部2', 3);
    
    -- 创建员工表
    CREATE TABLE employee (
      employee_id INTEGER PRIMARY KEY,
      employee_name VARCHAR(50),
      department_id INTEGER REFERENCES department(department_id)
    );
    
    -- 插入员工数据
    INSERT INTO employee VALUES (1, '张三', 2);
    INSERT INTO employee VALUES (2, '李四', 2);
    INSERT INTO employee VALUES (3, '王五', 4);
    INSERT INTO employee VALUES (4, '赵六', 5);
    INSERT INTO employee VALUES (5, '钱七', 6);
    INSERT INTO employee VALUES (6, '孙八', 7);

#### Oracle多表递归查询 ####

-- 使用递归查询获取部门的所有下级部门
    WITH subdepartments (department_id, sub_department_id, level) AS (
      SELECT department_id, department_id, 1
      FROM department
      WHERE department_id = 1 -- 以总公司为例
      UNION ALL
      SELECT d.department_id, sd.sub_department_id, s.level + 1
      FROM department d
      JOIN subdepartments s ON d.parent_department_id = s.sub_department_id
      JOIN subdepartments sd ON d.department_id = sd.department_id
    )
    SELECT department_id, sub_department_id, level
    FROM subdepartments;

#### MySQL多表递归查询 ####

-- 使用递归查询获取部门的所有下级部门
    WITH RECURSIVE subdepartments (department_id, sub_department_id, level) AS (
      SELECT department_id, department_id, 1
      FROM department
      WHERE department_id = 1 -- 以总公司为例
      UNION ALL
      SELECT d.department_id, sd.sub_department_id, s.level + 1
      FROM department d
      JOIN subdepartments s ON d.parent_department_id = s.sub_department_id
      JOIN subdepartments sd ON d.department_id = sd.department_id
    )
    SELECT department_id, sub_department_id, level
    FROM subdepartments;

#### SQL Server多表递归查询 ####

#### PostGreSQL多表递归查询 ####