一、根据id、pid(父id)递归向下查询

在 PostgreSQL 中，如果你有一个包含 id 和 pid 字段的行政区划表，并且你想查询某条数据及其所有下级数据（即该数据的所有子节点、孙节点等），你可以使用递归公用表表达式（Common Table Expressions, CTE）来实现这个需求。递归CTE可以让你从一个起始点开始，通过递归地连接自身来遍历层次结构。

WITH RECURSIVE sub_districts AS (
SELECT ID,pid,NAME,code,LEVEL FROM jweb_sys_org WHERE ID IN (530100,530300) UNION ALL
SELECT d.ID,d.pid,d.NAME,d.code,d.LEVEL FROM jweb_sys_org d INNER JOIN sub_districts sd ON d.pid=sd.ID)
SELECT*FROM sub_districts;
  • 首先，我们定义了一个名为 sub_districts 的递归CTE。
  • 初始查询部分用于选择你感兴趣的起始节点（在这里是 id=530100,530300 的记录，请根据实际情况修改）。
  • 递归部分通过将 districts 表与 sub_districts 自身连接来找到所有子节点。这里的关键在于 ON d.pid = sd.id，它表示“找到所有父节点ID等于当前CTE结果集中任何ID的记录”。
  • 最后，我们执行对 sub_districts 的查询以返回整个层次结构的结果集。

二、根据id、pid(父id)递归向上查询

如果想要查询某条数据及其所有上级数据（即该数据的所有父节点、祖父节点等），你可以使用PostgreSQL的递归公用表表达式（CTE）来实现。这种方法允许你从一个特定的节点开始，然后向上遍历其所有的父节点。

WITH RECURSIVE parent_districts AS (
SELECT ID,pid,NAME,code,LEVEL FROM jweb_sys_org WHERE ID=530102 UNION ALL
SELECT d.ID,d.pid,d.NAME,d.code,d.LEVEL FROM jweb_sys_org d INNER JOIN parent_districts pd ON d.ID=pd.pid)
SELECT*FROM parent_districts;
  • 首先定义了一个名为 parent_districts 的递归CTE。
  • 初始查询部分用于选择你感兴趣的起始节点（在这个例子中是 id=530102 的记录，请根据实际情况修改为目标ID）。
  • 递归部分通过将 districts 表与 parent_districts 自身连接来找到所有父节点。这里的关键在于 ON d.id = pd.pid，它表示“找到所有ID等于当前CTE结果集中任何记录的pid值的记录”，从而达到向上查找的目的。
  • 最后，我们执行对 parent_districts 的查询以返回包含起始节点及其所有上级节点的结果集。