postgresql查询子孙数据_PostgreSQL通过WITH查询（公用表表达式,CTE）遍历树结构

需求场景

行政区划&＃xff0c;机构、人员组织结构等信息&＃xff0c;天然符合树状结构&＃xff0c;在数据库表中通常会以ID和上级ID来存储其上下级隶属关系。业务场景则常常会需要对这类表”顺藤摸瓜“&＃xff0c;通过一个结点来查询其所有祖先或者子孙结点&＃xff0c;比如通过某人逐级查询出其所有直接、间接的上级领导&＃xff0c;或直接、间接的下级员工。传统SQL对此类需求只能通过存储过程或外部程序迭代发出多个查询来完成&＃xff0c;如果应用环境只允许用户运用单一的SQL语句&＃xff0c;则用户将无法完成遍历树的查询任务。SQL:1999标准推出了公用表表达式&＃xff08;Common Table Expression, CTE&＃xff09;的概念&＃xff0c;可由WITH查询定义CTE&＃xff0c;其中递归&＃xff08;Recursive&＃xff09;形式的CTE可用来遍历树结构。下面我们以PostgreSQL&＃xff08;从8.4版本开始支持CTE&＃xff09;为平台&＃xff0c;来讲解如何用CTE做树结构遍历。

WITH查询&＃xff08;公用表表达式&＃xff09;

WITH为较大的查询提供了书写辅助语句的手段&＃xff0c;辅助语句经常以公用表表达式&＃xff08;Common Table Expression, 缩写为CTE&＃xff09;形式被引用。WITH可将多条辅助语句附着在一条主语句上&＃xff0c;辅助语句可以是SELECT, INSERT, UPDATE, DELETE&＃xff0c;即增删改查&＃xff0c;主语句也可以是增删改查。

示例1&＃xff1a;

示例1中建立了两个公用表表达式&＃xff1a;cte1和cte2&＃xff0c;其中cte2中还引用了cte1&＃xff0c;即把cte1的结果当作FROM的来源&＃xff0c;而主查询则同时引用了cte1和cte2&＃xff0c;并用其做连接。其中cte2在表名后重新对位定义了查询结果的列名分别为x, y, z&＃xff0c;而cte1的列名则依赖于其内部查询。可以认为CTE是为单个查询定义的临时表&＃xff0c;它不存储为实际的数据表&＃xff0c;只在查询期间有效&＃xff0c;并且可以自引用&＃xff0c;可以被多次引用。

递归的WITH查询

如需要使用可自引用的CTE&＃xff0c;则要在WITH后加入RECURSIVE修饰符。

示例2&＃xff1a;

示例2中&＃xff0c;查询代码由多个SELECT经由UNION&＃xff08;或UNION ALL&＃xff09;连接而成&＃xff0c;其中最后一个UNION&＃xff08;或UNION ALL&＃xff09;之后的SELECT为递归项&＃xff0c;可以使用自引用&＃xff08;即可以在其中使用cte1&＃xff09;&＃xff0c;而在其之前不管经由几个UNION&＃xff08;或UNION ALL&＃xff09;合并&＃xff0c;均为非递归项&＃xff0c;不能使用自引用。RECURSIVE是必须要写的&＃xff0c;不然系统会报错。对于没有做自引用的查询&＃xff0c;依然可以用WITH RECURSIVE做引导。递归项通过CTE名称实现多轮的迭代自引用&＃xff0c;每一轮引用上一轮的结果&＃xff0c;第1轮引用非递归项的结果。虽然实现方式其实是迭代&＃xff0c;但是SQL标准委员会选择使用“递归”作为术语。

按照PostgreSQL官方文档的描述&＃xff0c;递归CTE的逻辑执行流程大概如下图&＃xff1a;

结合上图的逻辑流程&＃xff0c;以示例2为例&＃xff0c;其执行情况见下图&＃xff1a;

遍历特定的行政区划树

既然可以多次引用上轮查询的结果&＃xff0c;那么迭代遍历树也可得以实现。以行政区划为例&＃xff0c;先建立行政区划信息表&＃xff1a;

ad_code为行政区划代码。parent_ad_code为上级代码&＃xff0c;如无上级则取NULL。

现给定行政区划代码&＃xff08;如“沈阳市”&＃xff09;&＃xff0c;要查询其本身&＃xff08;以“C”标识&＃xff09;&＃xff0c;以及其所有上级&＃xff08;以“A”标识&＃xff09;和所有下级&＃xff08;以“S”标识&＃xff09;行政区划信息。查询代码如示例3&＃xff1a;

示例3&＃xff1a;

主查询只是简单的从cte1和cte2中取全部数据并在合并后做了排序。

cte1的非递归项负责查询指定行政区划代码的信息&＃xff0c;递归项通过内连接&＃xff0c;由非递归项出发逐级查询每个行政区划代码的上级信息&＃xff0c;当上级代码为NULL时&＃xff0c;由于任何行政区划的代码都不为NULL&＃xff0c;因此内连接结果为0行的空表&＃xff0c;迭代查询结束。

cte2的非递归项负责查询指定行政区划代码的下级行政区划&＃xff0c;递归项通过内连接&＃xff0c;由非递归项出发逐级查询指定行政区划的历级下级行政区划信息&＃xff0c;当某次查询提供的行政区划代码通过内连接连不到任何上级代码时&＃xff0c;内连接结果为0行的空表&＃xff0c;迭代查询结束。

查询结果&＃xff1a;

PostgreSQL官方文档参见&＃xff1a;