withrecursive用法详解

一、基础知识

1、withrecursive是SQL中递归查询的功能，它允许用户在一个查询中多次引用同一查询并不断地生成临时表，实现递归查询的功能。

2、withrecursive的语法格式为：WITH RECURSIVE 临时表名 AS (查询语句), SELECT 语句;

3、withrecursive由两部分组成：第一部分是与文名称定义了一个临时表，用于存储所有生成的结果；第二部分是SELECT语句，用于查询这个临时表中的数据。

二、使用withrecursive实现递归查询

1、假设有一张员工表employee，里面记录了员工的ID、上级ID和姓名。要求查找某个员工的所有下属，包括下属的下属。

WITH RECURSIVE subordinates (id,name) AS (
    SELECT id,name FROM employee WHERE id = 1 --假设要查找id=1的员工所有下属
    UNION ALL
    SELECT employee.id,employee.name FROM employee,subordinates WHERE employee.supervisor_id=subordinates.id
)
SELECT * FROM subordinates;

2、上述代码中，定义了一个名为subordinates的临时表，SELECT语句查询了这个临时表。查询语句是两个SELECT语句的组合：

第一个SELECT查询了id为1的员工，并将其加入临时表中。

第二个SELECT语句查询了employee表中所有上级为前一步查询结果中成员的员工，并将它们加入临时表中。这个过程由关键字UNION ALL连接，表示将两个查询结果合并，不去重。

递归查询的过程就这样进行下去，直到没有满足条件的数据为止。

三、使用withrecursive实现递归查询带层级

1、如果要查找员工表中每个员工的姓名以及他们的上下级关系，可以在subordinates表中增加一个level字段来记录员工的层级:

WITH RECURSIVE subordinates (id,name,level) AS (
    SELECT id,name,0 FROM employee WHERE id = 1 --假设要查找id=1的员工所有下属
    UNION ALL
    SELECT employee.id,employee.name,subordinates.level+1 FROM employee,subordinates WHERE employee.supervisor_id=subordinates.id
)
SELECT * FROM subordinates;

2、新增的第三个字段level表示员工所在的层数，它的值是前一行的level加1，这样创建了一个新的记录并加入subordinates表中。递归过程同样在UNION ALL之间进行。

四、使用withrecursive实现递归查询找到根节点

1、以上查询方式只能查找某个员工的下属，如果要求查找某个员工的上级链，可以倒序开始查询，并将查询结果反转。

WITH RECURSIVE superiors(id,name,supervisor_id,level) AS (
    SELECT id,name,supervisor_id,0 FROM employee WHERE id = 9 --假设要查找id=9的员工的上级链
UNION ALL
    SELECT employee.id,employee.name,employee.supervisor_id,superiors.level+1 FROM employee,superiors WHERE superiors.supervisor_id=employee.id
)
SELECT id,name FROM superiors ORDER BY level DESC;

2、以上查询首先选取id=9的员工，然后逐级向上查询其上级，直到没有上级为止。

3、这次查询与上次查询的区别在于查询结果按层级逆序排序，从而实现了查询员工的上级链。

五、使用withrecursive实现递归查询自身关联表树形结构

1、在自身关联表中使用withrecursive来查询树形结构，例如商品分类表category，每个分类有一个父类别，将其处理成树形结构。

WITH RECURSIVE category_tree(id,name,parent_id,level,pathinfo) AS (
    SELECT id,name,parent_id, 1, cast(id as varchar(255)) as pathinfo FROM category WHERE parent_id is null 
    UNION ALL 
    SELECT category.id,category.name,category.parent_id, category_tree.level+1, pathinfo || ',' || category.id FROM category,category_tree WHERE category.parent_id = category_tree.id 
)
SELECT id,name,level,pathinfo FROM category_tree ORDER BY pathinfo;

2、以上语句中，定义了名为category_tree的临时表，包含id、name、parent_id、level、pathinfo 5个字段，SELECT语句查询category_tree表中的所有数据，并按照pathinfo字段排序。

3、第一个SELECT语句中，选取所有parent_id为null的记录，并将其放入子表中。每个记录的初始层级是1，路径信息为父类别id；

4、第二个SELECT语句连接了category和category_tree表，在每次查询中将category表中的结果并入category_tree中，并根据父类别id逐层向下查询，并将级别加1，路径信息加入父类别路径末尾。

六、总结

1、withrecursive是SQL中递归查询的主要功能，可以帮助用户实现各种复杂的查询和计算任务。

2、使用withrecursive实现递归查询需要考虑查询的逻辑和关联关系，同时需要对withrecursive的语法和参数有一定的掌握。

原创文章，作者：小蓝，如若转载，请注明出处：https://www.506064.com/n/236113.html