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

最新推荐文章于 2024-03-18 11:01:27 发布

weixin_39979489

最新推荐文章于 2024-03-18 11:01:27 发布

阅读量698

点赞数

需求场景

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

WITH查询（公用表表达式）

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

示例1：

WITH cte1 AS (
    SELECT ...
    FROM xxx
    ...
),cte2(x,y,z) AS (
    SELECT ...
    FROM cte1
    ...
)
SELECT ...
FROM cte1 t1
INNER JOIN cte2 t2
ON
...
;

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

递归的WITH查询

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

示例2：

WITH RECURSIVE
cte1 AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n+1 AS n
    FROM cte1
    WHERE n<3
)
SELECT *
FROM cte1;

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

按照PostgreSQL官方文档的描述，递归CTE的逻辑执行流程大概如下图：

递归CTE的逻辑执行流程

结合上图的逻辑流程，以示例2为例，其执行情况见下图：

示例2递归CTE的逻辑执行流程

遍历特定的行政区划树

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

CREATE TABLE IF NOT EXISTS ad_info(
    ad_code VARCHAR(6) PRIMARY KEY,
    parent_ad_code varchar(6),
    ad_level smallint,
    ad_name text
);
/*县级以上行政区划代码信息可在民政部网站下载*/
INSERT INTO ad_info(ad_code,ad_level,ad_name,parent_ad_code)
VALUES 
('100000',0,'中华人民共和国',NULL),
('110000',1,'北京市','100000'),
('110100',2,'北京市辖区','110000'),
('110101',3,'东城区','110100'),

...

('659004',3,'五家渠市','659000'),
('710000',1,'台湾省','100000'),
('810000',1,'香港特别行政区','100000'),
('820000',1,'澳门特别行政区','100000');

ad_code为行政区划代码。parent_ad_code为上级代码，如无上级则取NULL。

现给定行政区划代码（如“沈阳市”），要查询其本身（以“C”标识），以及其所有上级（以“A”标识）和所有下级（以“S”标识）行政区划信息。查询代码如示例3：

示例3：

WITH RECURSIVE
cte1(
    ad_code,
    ad_level,
    ad_name,
    parent_ad_code,
    ad_type
) AS (
    SELECT
        ad_code,
        ad_level,
        ad_name,
        parent_ad_code,
        cast('C' as varchar(1))
    FROM ad_info
    WHERE ad_code='210100'
    UNION ALL
    SELECT
        t1.ad_code,
        t1.ad_level,
        t1.ad_name,
        t1.parent_ad_code,
        cast('A' as varchar(1))
    FROM ad_info t1
    INNER JOIN cte1
    ON t1.ad_code=cte1.parent_ad_code
),
cte2 AS (
    SELECT
        ad_code,
        ad_level,
        ad_name,
        parent_ad_code,
        cast('S' as varchar(1)) AS ad_type
    FROM ad_info
    WHERE parent_ad_code='210100'
    UNION ALL
    SELECT
        t1.ad_code,
        t1.ad_level,
        t1.ad_name,
        t1.parent_ad_code,
        cast('S' as varchar(1)) AS ad_type
    FROM ad_info t1
    INNER JOIN cte2
    ON t1.parent_ad_code=cte2.ad_code
)
SELECT * FROM cte1
UNION ALL
SELECT * FROM cte2
ORDER BY ad_type,ad_level,ad_code
;

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

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

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

查询结果：