注:本文使用的是最新版的PgSQL(2023.06.07)
问题背景:在公司内的B2B电商系统中,下游交易环节,需要用户选择地址,而我采用了 省、市、区、街道 四级地址结构,例如:广东省→广州市→海珠区→江海街道。这是一条完整的地址层级关系。此时引入了Github中比较新(2023年6月4日更新)的地址关系csv。为了减少前端调用接口的次数,我选择在选择 xx市 之后使用递归查询将街道信息一并查出给前端。
地址表和转换SQL教程GitHub链接如下:
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)。
Step4:表中实践。
接下来,我们直接在表中实践一下。
上图!
可以看出,通过这个递归语句,直接将parent_id为4401(广州市)的子节点、叶子节点数据全部查出来了。
总结:
1. 在已知级数切级数较少的多级子节点查询中,我们一般可以直接采用子查询的方式来完成多级树型结构的组装。
2. 对于多级树型结构和未知级数的树形结构,我们可以使用递归CTE查询的方法来完成所有子节点的查询。
3. (重要)CTE不等于临时表,具体的区别如下:
3.1. 生命周期不同:CTE 的生命周期仅限于当前 SQL 查询,即一旦完成定义并在查询中使用,CTE 就会被自动销毁。
而临时表在会话级别存在,这意味着在当前会话 (SQL Server是全局) 中可以在多个查询间重复使用临时表。当会话结束时,临时表会被销毁。
3.2. 使用场景不同:CTE 用于简化复杂的查询语句,提高可读性。CTE 的特点是可以方便地创建递归查询,处理层次结构数据 等。
临时表主要用于在多个查询之间存储和共享数据。特别是当需要在存储过程、函数或触发器中重复使用这些数据时,临时表优势非常明显。
3.3. 性能:CTE 的性能优势在于处理子查询和递归查询,因为它容易优化,减少了整体查询操作的复杂性。对于递归查询、层次结构查询等场景,使用 CTE 可以提高查询的性能。
临时表的性能优点在于存储和重用中间结果。当数据量大时,临时表可能消耗更多资源,但在多个查询中重复使用时,它可以降低对数据的再次计算,提高性能。然而,在处理递归查询等场景时,临时表可能不如 CTE 高效。