从概念到实践，带你掌握层次递归查询

本文主要以GaussDB数据库为实验平台，为大家讲解层次递归查询使用方法。

华为云开发者联盟

347人浏览 · 2023-12-08 17:50:24

华为云开发者联盟 · 2023-12-08 17:50:24 发布

本文分享自华为云社区《GaussDB 数据库 SQL 系列 - 层次递归本文主要以GaussDB数据库为实验平台，为大家讲解层次递归查询使用方法。GaussDB 数据库 SQL 系列 - 层次递归》，作者： Gauss 松鼠会小助手 2。

一、前言

层次递归查询是一种常见的 SQL 查询方式，特别是在一些层次化的数据存储结构中经常用到。本文主要以 GaussDB 数据库为实验平台，为大家讲解其使用方法。

二、GuassDB 数据库层次递归查询概念

层次化结构可以理解为树状数据结构，由节点构成。举个简单的例子，如下图所示，由子节点向上查询根节点，或者由根节点遍历所有子节点：

递归查询是指查询中需要多次调用自身的查询方式。在递归查询中，查询会反复地递归进入到一个子查询中，直到查询得到满足条件的结果或遍历完整个查询范围。递归查询在数据库领域中有着重要的应用。方便数据处理，简化开发代码。

在 GaussDB 数据库中，递归查询可以通过使用 “select…start with…connect by…prior…” 和 “WITH RECURSIVE” 语法来实现。

三、GaussDB 数据库层次递归查询实验示例

1、创建实验表

--创建实验表

CREATE TABLE area(

a_code VARCHAR(10)

,a_name VARCHAR(10)

,p_a_code VARCHAR(10)

,a_level INT);
--插入测试数据

INSERT INTO area VALUES('610000','陕西省','0','1');

INSERT INTO area VALUES('610100','西安市','610000','2');

INSERT INTO area VALUES('610101','市辖区','610100','3');

INSERT INTO area VALUES('610102','新城区','610100','3');

INSERT INTO area VALUES('610103','碑林区','610100','3');

INSERT INTO area VALUES('610104','莲湖区','610100','3');

INSERT INTO area VALUES('610111','灞桥区','610100','3');

INSERT INTO area VALUES('610112','未央区','610100','3');

INSERT INTO area VALUES('610113','雁塔区','610100','3');

INSERT INTO area VALUES('610114','阎良区','610100','3');

INSERT INTO area VALUES('610115','临潼区','610100','3');

INSERT INTO area VALUES('610116','长安区','610100','3');

INSERT INTO area VALUES('610122','蓝田县','610100','3');

INSERT INTO area VALUES('610124','周至县','610100','3');

INSERT INTO area VALUES('610125','鄠邑区','610100','3');

INSERT INTO area VALUES('610126','高陵区','610100','3');
--查看初始化结果

SELECT * FROM area;

2、sys_connect_by_path(col, separator)

描述：返回从根节点到当前行的连接路径。

参数：col 为在路径中显示的列名，支持类型为 CHAR/VARCHAR/NVARCHAR2/TEXT 的列，参数 separator 为路径节点之间的分隔符。

返回值类型：text

示例：

-返回从根节点到当前行的连接路径

SELECT *, sys_connect_by_path(a_name, '-') FROM area start with a_code ='610000' connect by prior a_code = p_a_code;

3、connect_by_root(col)

描述：返回当前行的根节点值。

参数：col 为输出列的名称。

返回值类型：即为所指定列 col 的数据类型。

示例：

--返回当前行的根节点值。

SELECT *, connect_by_root(a_name) FROM area start with a_code ='610000' connect by prior a_code = p_a_code;

4、WITH RECURSIVE

使用 WITH RECURSIVE 关键字，：

--使用WITH RECURSIVE

WITH RECURSIVE t_area AS (

SELECT a_level,a_code,p_a_code,a_name, a_name ::varchar(50) AS path FROM area WHERE p_a_code = '0'

UNION ALL

SELECT t2.a_level+1,t1.a_code,t1.p_a_code, t1.a_name,CONCAT(t2.path, ',', t1.a_name) ::varchar(50) AS path FROM area t1 JOIN t_area t2 ON t1.p_a_code=t2.a_code

) SELECT * FROM t_area;

示例说明：这个查询使用了递归表达式来遍历省级行政区域关系。表达式使用了两个 SELECT 语句：第一个 SELECT 语句选取了所有父级代码为 0 的行政区域信息，并将它们添加到临时表 t_area 中。它们的层级选取初始化的 a_level 级，并且它们的路径被设置为它们的行政区名 a_name。这个 SELECT 语句是递归查询的起点。第二个 SELECT 语句连接了 area 表和 t_area 表。它选取了 area 表中所有具有父级行政区，并连接到 t_area 表中已经存在的行政区。对于每个连接的行，它们的层级是父级的层级加 1，并且它们的路径是父级的路径加上逗号和它们自己的行政区。查询结果返回 t_area 表中所有的行政区信息。

（“::varchar (50)” 是创建实验表时的字符长度不够，需要重新定义，二是两个 SELECT 语句使用 UNION ALL 连接，需要保持类型长度一致）。