当前位置：首页 > news >正文

【postgresql数据库实现表的树结构查询】

news 2025/7/15 13:53:09

postgresql数据库实现表的树结构查询：

示例数据库表：data_base_basin流域树树结构表

在这里插入图片描述

SQl语句实现树结构

重点函数：WITH RECURSIVE BasinTree AS 。。。
在 PostgreSQL 中，WITH RECURSIVE 是用于实现递归查询的功能。它允许你在一个 SQL 查询中反复执行某个子查询，直到满足特定的结束条件。这非常适合处理具有层级结构的数据（如树形结构、图结构）。

什么是 WITH RECURSIVE？
WITH RECURSIVE 是 PostgreSQL 提供的一种公用表表达式（CTE, Common Table Expression），支持递归操作。

递归 CTE 的基本结构：

WITH RECURSIVE cte_name AS (-- 初始查询（非递归部分）SELECT ...UNION ALL-- 递归查询部分SELECT ...FROM cte_nameWHERE ...
)
SELECT * FROM cte_name;

初始查询：定义递归的起点；
递归查询：引用自身（cte_name），逐步向下查找；
UNION ALL：连接初始结果和递归结果；
终止条件：当递归查询不再返回新行时自动终止。

示例0：表树结构查询：

        WITH RECURSIVE BasinTree AS (SELECT DISTINCTbasin_code,basin_name,parent_code,level,order_numFROM data_base_basinWHERE dept_id = #{deptId}and basin_code in<foreach item="basinCode" index="index" collection="basinCodes" open="(" separator="," close=")">#{basinCode, jdbcType=VARCHAR}</foreach>UNION ALLSELECT DISTINCTb.basin_code,b.basin_name,b.parent_code,b.level,b.order_numFROM data_base_basin bINNER JOIN BasinTree t ON b.basin_code = t.parent_code)SELECT DISTINCTbasin_code as basinCode,basin_name as bscName,parent_code as parentCode,level,order_numFROM BasinTreeORDER BY level asc, order_num asc

示例1：生成数字序列（简单演示）

WITH RECURSIVE numbers AS (SELECT 1 AS nUNION ALLSELECT n + 1 FROM numbers WHERE n < 5
)
SELECT * FROM numbers;

输出：

n
---
1
2
3
4
5

示例 2：查询组织架构中的员工上下级关系（树形结构）

CREATE TABLE employee (id INT PRIMARY KEY,name TEXT,manager_id INT
);

在这里插入图片描述
现在想查出所有属于 Alice（id=1）管理链下的员工，包括间接下属。

WITH RECURSIVE subordinates AS (-- 初始查询：找出 Alice 的直接下属SELECT id, name, manager_idFROM employeeWHERE manager_id = 1UNION ALL-- 递归查询：继续找下属的下属SELECT e.id, e.name, e.manager_idFROM employee eINNER JOIN subordinates s ON e.manager_id = s.id
)
-- 最终查询：合并所有结果
SELECT * FROM subordinates;

输出：

id | name   | manager_id
---|--------|-----------
2  | Bob    | 1
3  | Carol  | 2
4  | David  | 2

示例 3：查询路径或层级信息（带深度）

你可以添加一个字段来记录层级深度：

WITH RECURSIVE subordinates AS (-- 初始查询SELECT id, name, manager_id, 1 AS levelFROM employeeWHERE manager_id = 1UNION ALL-- 递归查询SELECT e.id, e.name, e.manager_id, s.level + 1FROM employee eINNER JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;

输出：

id | name   | manager_id | level
---|--------|------------|-------
2  | Bob    | 1          | 1
3  | Carol  | 2          | 2
4  | David  | 2          | 2