公共表表达式CTE递归查询学习篇1：WITH RECURSIVE关键字

最新推荐文章于 2024-05-10 08:00:00 发布

ToneyMa

最新推荐文章于 2024-05-10 08:00:00 发布

阅读量333

点赞数

文章标签： sql

本文链接：https://blog.csdn.net/Mccson/article/details/131070965

版权

注：本文使用的是最新版的PgSQL(2023.06.07)

问题背景：在公司内的B2B电商系统中，下游交易环节，需要用户选择地址，而我采用了省、市、区、街道四级地址结构，例如：广东省→广州市→海珠区→江海街道。这是一条完整的地址层级关系。此时引入了Github中比较新(2023年6月4日更新)的地址关系csv。为了减少前端调用接口的次数，我选择在选择 xx市 之后使用递归查询将街道信息一并查出给前端。

地址表和转换SQL教程GitHub链接如下：

GitHub - xiangyuecn/AreaCity-JsSpider-StatsGov: 省市区县乡镇三级或四级城市数据，带拼音标注、坐标、行政区域边界范围；2023年06月04日最新采集，提供csv格式文件，支持在线转成多级联动js代码、通用json格式，提供软件转成shp、geojson、sql、导入数据库；带浏览器里面运行的js采集源码，综合了中华人民共和国民政部、国家统计局、高德地图、腾讯地图行政区划数据https://github.com/xiangyuecn/AreaCity-JsSpider-StatsGov

Step1：了解表结构

直接上表结构：

表采用了四级地址结构：省、市、区(县)、街道。

Step2：了解需求：根据父级id递归查询所有子级目录。

熟悉表结构后，我们就可以开始递归查询实践了。

首先我们以广州市为例：id=4401。

需求：已知广州市主键id，我们要查出所有和广州市有关的子级数据。

那么，按照我们最简单、最常规的方法，就是通过parent_id去匹配后面两个子级的数据：
#查出三级(区)地址
SELECT * FROM b2b_area WHERE parent_id = 4401;
#查出四级(街道)地址，其中l2.id是三级地址的id
SELECT * FROM b2b_area WHERE parent_id = l3.id;
这里，我们使用了两个查询来完成广州市的子级地址查询。

1.问：但是，这不是也可以通过子查询的方法来完成吗？

1.答：是的，但这仅限于你在知道表中数据的层次情况下，才能通过子查询的方式完成，万一表的层次结构有10级怎么办？那就得写10个子查询。如果我压根不知道他到底有几层结构呢？很显然，用 子查询的方法 对于 多层级结构的数据 和 未知层级结构的数据表 来说，并不通用。

2.问：那对于这两种数据，该怎么解决？

2.答：用我们今天的主角《CTE公共表拓展：递归表达式》。

Step3：使用CTE递归查出所有子级数据。

直接上代码：

WITH RECURSIVE addr_hierarchy(id, name, parent_id, level) AS (
    -- 基本情况（市级地址）
    SELECT id, name, parent_id, area_level
    FROM b2b_area
    WHERE parent_id = 4401
  UNION ALL
    -- 递归情况
    SELECT ch.id, ch.name, ch.parent_id, h.level + 1
    FROM b2b_area ch
    JOIN addr_hierarchy h ON ch.parent_id = h.id
)
SELECT * FROM addr_hierarchy ORDER BY level, id;

上面这段SQL就是使用了CTE中的递归表达式。

那么接下来一一解释一下这几个名词：

CTE：公共表表达式，简单的说它是一个 命名的临时结果集 ，可以将它简单的理解为一个语句级的临时表。

但是它不等于临时表！不等于临时表！不等于临时表！

递归CTE：是一种CTE的拓展，可以理解为通过递归查询的形式生成一个临时表。如上面的SQL代码所示。

接下来具体解释一下递归CTE：

递归CTE的基本写法格式如下：
WITH RECURSIVE <临时表名>(<自定义字段名1>, <自定义字段名2>, ...) AS (
    -- 基本情况查询语句
    SELECT xxx 
    FROM xxx 
    WHERE xxx = xxx
  UNION ALL
    -- 递归情况
    SELECT xxx ,level + 1
    FROM xxx
    JOIN <临时表名> ON xxx = xxx
)
-- 查询出最终结果
SELECT * FROM <临时表名> ORDER BY xxx;
这是一个最基本的递归CTE写法，其中：

基本情况：一般是查询结构树的某个父节点的数据

递归情况：根据父节点的数据(比如id)，根据特定条件查询该父节点下的子节点和叶子节点(比如parent_id = 父节点.id)。