从概念到实践,带你掌握层次递归查询
本文分享自华为云社区《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 连接,需要保持类型长度一致)。
四、递归查询的优缺点
1、优点
递归查询能够简化应用程序代码,方便对数据结构的处理。在一些复杂的查询场景中,递归查询能够更快地得到结果。适用于各种类型的树形结构。
2、缺点
递归查询有时可能会产生很多次递归调用,导致性能下降。算法通常比其他方法更复杂,编写比较困难。不适合处理大型数据集。
五、总结
递归查询是一种非常实用的查询方法,在处理分层数据、树形数据等复杂查询场景中非常广泛。但是,在使用递归查询时需要注意一些问题:
• 必须合理控制递归深度,防止过度递归。
• 最好不要在递归查询中执行复杂的计算和组合操作,避免占用过多资源。
• 避免在递归查询中使用ORDER BY操作,这会大大降低性能。
• 在使用递归查询时,应该谨慎处理好死循环问题。
同样的, 在使用GaussDB等数据库时,只要正确合理的应用递归查询,就可以更好地提高查询效率和应用性能。
低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。
持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。
转载内容版权归作者及来源网站所有,本站原创内容转载请注明来源。
- 上一篇
GaussDB SQL基础语法示例-常见的条件表达式
目录 一、前言 二、条件表达式的概念及GaussDB中的常见条件表达式 三、GaussDB中常用的条件表达式(语法 + 示例) 1、CASE表达式 2、DECODE表达式 3、COALESCE表达式 4、NULLIF表达式 5、GREATEST/ LEAST表达式 6、NVL表达式 四、小结 一、前言 SQL是用于访问和处理数据库的标准计算机语言。GaussDB支持SQL标准(默认支持SQL2、SQL3和SQL4的主要特性)。 本系列将以《云数据库GaussDB—SQL参考》为主线进行介绍。 二、条件表达式的概念及GaussDB中的常见条件表达式 条件表达式是指在数据库中进行SQL语句查询时,根据特定条件筛选出符合要求的数据所使用的表达式。 在GaussDB数据库中,CASE、DECODE、COALESCE、NULLIF、GREATEST和NVL等都是常用的条件表达式。 CASE:根据条件进行多分支判断,根据不同的条件返回不同的结果。 DECODE:GaussDB数据库提供的函数功能,相当于SQL语言中的IF-THEN-ELSE语句,根据第一个参数和后续参数进行比较,返回符合条件的结果...
- 下一篇
Sermant:无代理服务网格架构解析及无门槛玩转插件开发
本文分享自华为云社区《Sermant:无代理服务网格架构解析及无门槛玩转插件开发》,作者: 华为云社区精选 。 本期直播的主题是《从架构设计到开发实践,深入浅出了解Sermant》,华为云云原生DTSE技术布道师、华为云高级工程师、Sermant开源社区PMC核心成员栾文飞,为广大开发者详细从架构设计角度介绍了Sermant的类加载器架构和插件化机制,并在实战中从基础能力开发,到进阶使用统一动态配置能力、统一日志能力一步步完成插件开发。 Sermant核心机制——插件化 Sermant分为两层,Sermant框架层和Sermant服务治理层,Sermant框架提供了一系列的API用于插件开发者可以快速进行服务治理插件的开发。 服务治理层是由多种不同的服务治理能力组成,包含流量控制、标签路由等都是被包装在不同的插件中。其中还包括Backend可用于一些状态展示和事件监控管理。 Sermant的插件化机制中得益于Java 的SPI机制,在很多高可扩展的的项目中,都会利用SPI去加载自己的扩展,Sermant在框架中定义插件声明接口,用于加载不同的服务治理插件,插件开发者只需要按照接口契约,...
相关文章
文章评论
共有0条评论来说两句吧...
文章二维码
点击排行
推荐阅读
最新文章
- SpringBoot2更换Tomcat为Jetty,小型站点的福音
- CentOS6,CentOS7官方镜像安装Oracle11G
- CentOS关闭SELinux安全模块
- 设置Eclipse缩进为4个空格,增强代码规范
- Docker快速安装Oracle11G,搭建oracle11g学习环境
- CentOS8,CentOS7,CentOS6编译安装Redis5.0.7
- Springboot2将连接池hikari替换为druid,体验最强大的数据库连接池
- SpringBoot2配置默认Tomcat设置,开启更多高级功能
- CentOS7,8上快速安装Gitea,搭建Git服务器
- CentOS7编译安装Gcc9.2.0,解决mysql等软件编译问题