postgresql 的递归查询

这篇具有很好参考价值的文章主要介绍了postgresql 的递归查询。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

postgresql 的递归查询功能很强大,可以实现传统 sql 无法实现的事情。那递归查询的执行逻辑是什么呢?在递归查询中,我们一般会用到 union 或者 union all,他们两者之间的区别是什么呢?

递归查询的执行逻辑

递归查询的基本语法如下

WITH RECURSIVE ctename AS (
   SELECT /* non-recursive branch, cannot reference "ctename" */
   UNION [ALL]
   SELECT /* recursive branch referencing "ctename" */
)
SELECT ...
FROM ctename ...

其本身也是一个CTE,可以将复杂的查询逻辑进行分离,让整个查询的逻辑更加清晰。对于递归查询而言,分为两部分:

  1. 非递归部分。即例子中的 UNION [ALL] 的上半部分
  2. 递归部分。即例子中的 UNION [ALL] 的下半部分

递归查询的逻辑如下:

  1. 计算非递归部分,其结果将作为递归查询的数据集,也是初始数据集
  2. 在第一步计算出来的数据上,执行递归部分,新查询出的数据将作为下次递归执行的数据集。也就是说,每次递归使用的数据集都是上次递归的结果
  3. 直到没有新的数据产生后,递归结束
  4. 将每一次递归的数据进行聚合,就拿到了最终的数据集

UNION 和 UNION ALL

  1. UNION: 会将本次递归查询到的数据进行内部去重,也会和之前递归查询出的数据进行去重
  2. UNION ALL: 不会对数据进行去重

举个例子

// 创建表
create table document_directories
(
    id         bigserial                                          not null,
    name       text                                               not null,
    created_at timestamp with time zone default CURRENT_TIMESTAMP not null,
    updated_at timestamp with time zone default CURRENT_TIMESTAMP not null,
    parent_id  bigint                   default 0                 not null
);

// 插入示例数据,有两条数据是一样的
INSERT INTO public.document_directories (id, name, created_at, updated_at, parent_id) VALUES (1, '中国', '2020-03-28 15:55:27.137439', '2020-03-28 15:55:27.137439', 0);
INSERT INTO public.document_directories (id, name, created_at, updated_at, parent_id) VALUES (2, '上海', '2020-03-28 15:55:40.894773', '2020-03-28 15:55:40.894773', 1);
INSERT INTO public.document_directories (id, name, created_at, updated_at, parent_id) VALUES (3, '北京', '2020-03-28 15:55:53.631493', '2020-03-28 15:55:53.631493', 1);
INSERT INTO public.document_directories (id, name, created_at, updated_at, parent_id) VALUES (4, '南京', '2020-03-28 15:56:05.496985', '2020-03-28 15:56:05.496985', 1);
INSERT INTO public.document_directories (id, name, created_at, updated_at, parent_id) VALUES (5, '浦东新区', '2020-03-28 15:56:24.824672', '2020-03-28 15:56:24.824672', 2);
INSERT INTO public.document_directories (id, name, created_at, updated_at, parent_id) VALUES (6, '徐汇区', '2020-03-28 15:56:39.664924', '2020-03-28 15:56:39.664924', 2);
INSERT INTO public.document_directories (id, name, created_at, updated_at, parent_id) VALUES (6, '徐汇区', '2020-03-28 15:56:39.664924', '2020-03-28 15:56:39.664924', 2);

使用 UNION ALL 进行数据查询

with recursive sub_shanghai as (
     select id, name, parent_id
     from document_directories
     where id=2
     union all
     select dd.id, dd.name, dd.parent_id
     from document_directories dd
     join sub_shanghai on dd.parent_id=sub_shanghai.id
 )
 select * from sub_shanghai;

结果如下

postgresql 的递归查询,postgresql,sql,数据库

 

postgresql 的递归查询,postgresql,sql,数据库

使用 UNION 进行查询

with recursive sub_shanghai as (
     select id, name, parent_id
     from document_directories
     where id=2
     union
     select dd.id, dd.name, dd.parent_id
     from document_directories dd
     join sub_shanghai on dd.parent_id=sub_shanghai.id
 )
 select * from sub_shanghai;

得到结果如下

postgresql 的递归查询,postgresql,sql,数据库

 

postgresql 的递归查询,postgresql,sql,数据库

我们修改下原始数据,再看下去重逻辑的区别

update document_directories set parent_id = 2 where id=2;

当我们使用 UNION 进行递归查询时,结果并没有发生变化。但是当我们使用 UNION ALL 进行查询时,会一直执行。这是因为 UNION ALL 不会将数据进行去重,而每次递归查询的时候,总归能查询到 {"id": 5, name:"上海", "parent_id": 2} 这条数据,所以递归就没有终止条件。

从而也验证了,UNION 不但会将本次递归查询的数据进行内部去重,也会和之前的递归结果进行去重。文章来源地址https://www.toymoban.com/news/detail-661826.html

到了这里,关于postgresql 的递归查询的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • [运维|数据库] PostgreSQL数据库对MySQL的 READS SQL DATA 修饰符处理

    在 PostgreSQL 中,访问权限通常是通过数据库角色和表级别的权限进行管理,而不需要类似 MySQL 中的 READS SQL DATA 修饰符。 要在 PostgreSQL 中管理数据库对象的访问权限,您可以使用以下 SQL 命令: GRANT :授予用户或角色对表、视图等对象的特定权限。 REVOKE :撤销用户或角色对

    2024年02月07日
    浏览(70)
  • postgresql|数据库|批量执行SQL脚本文件的shell脚本

    对于数据库的维护而言,肯定是有SQL脚本的执行,例如,某个项目需要更新,那么,可能会有很多的SQL脚本需要执行,SQL脚本可能会包含有建表,插入数据,索引建立,约束建立,主外键建立等等内容。 那么,几个SQL脚本可能无所谓,navicat或者psql命令行 简简单单的就导入了

    2024年02月01日
    浏览(68)
  • MyBatis-Plus 查询PostgreSQL数据库jsonb类型保持原格式

    在这篇文章,我们保存了数据库的jsonb类型:MyBatis-Plus 实现PostgreSQL数据库jsonb类型的保存与查询 这篇文章介绍了模糊查询json/jsonb类型:PostgreSQL 查询json/jsonb是否存在某个片段 在模糊查询json/jsonb之前,我们得拿到正确的json/jsonb片段,比如我们模糊查询好几个连着的键值对,

    2024年02月15日
    浏览(58)
  • PostgreSQL数据库命令行执行SQL脚本的三种方式

    生成环境中,出于安全性等原因,往往不提供数据库连接工具,所以对数据库的更新和升级就得通过命令行来实现。本文总结了三种命令行执行sql脚本的方式。 命令格式: psql [option…] [dbname] [username] 常用参数介绍: -h:指定IP地址或主机名。 -p:指定端口,默认为5432。 -U:

    2024年02月11日
    浏览(61)
  • 【数据库】PostgreSQL中使用`SELECT DISTINCT`和`SUBSTRING`函数实现去重查询

    在PostgreSQL中,我们可以使用 SELECT DISTINCT 和 SUBSTRING 函数来实现对某个字段进行去重查询。本文将介绍如何使用这两个函数来实现对 resource_version 字段的去重查询。 1. SELECT DISTINCT 语句 SELECT DISTINCT 语句用于从表中选择不重复的记录。如果没有指定列名,则会选择所有列。在本

    2024年02月14日
    浏览(43)
  • 关系数据库SQL数据查询

    1.查询仅涉及一个表,选择表中的若干列 查询全部列 选出所有属性列: 在SELECT后面列出所有列名 将目标列表达式指定为 * 查询经过计算的值 SELECT子句的目标列表达式不仅可以为表中的属性列,也可以是表达式 使用列别名改变查询结果的列标题: 2.选择表中的若干元组

    2024年02月09日
    浏览(54)
  • 数据库作业-sql语句查询

    建表的插入数据的表 数据库作业-sql建表和插入数据_快乐的xiao何的博客-CSDN博客 create table supplier( supplierno char(6) primary key, suppliername nvarchar(10), address nvarchar(20), number char(11) )create table category( categoryno char(5) primary key, categoryname varchar(20), descriptions text... https://blog.csdn.net/m0_539670

    2023年04月23日
    浏览(54)
  • 数据库SQL查询相关练习

    1、显示所有职工的基本信息。 2、查询所有职工所属部门的部门号,不显示重复的部门号。 3、求出所有职工的人数。 4、列出最高工和最低工资。 5、列出职工的平均工资和总工资。 6、创建一个只有职工号、姓名和参加工作的新表,名为工作日期表。 8、列出所有姓刘的职工

    2024年01月25日
    浏览(47)
  • mysql数据库递归查询树形结构(适用场景:菜单多级分类,多级关联评论查询),用strea流把list转成树的方法详解

    层次关系: 现在的需求是把这个层级关系,在前端显示出来,后端的处理方法有两种: 1.直接把全部的数据从数据库中拿到,然后在java代码里面使用树形结构来进行解析,但是这种做法只能在数据量比较小的时候使用,然后数据量一大会造成内存溢出 2.在mysql中创建一个函数

    2024年02月05日
    浏览(40)
  • 【数据库】日常使用PL/SQL 登录ORACLE 数据库查询数据

    一、PL/SQL 登录方式 username: ##访问数据库的账号 password: ##访问数据库的密码 Databse: ##数据库IP地址/实例名 数据库集群心跳地址/实例名 Connect as : ##Normal,如果使用sysdba账户登录选择SYSDBA 二、PL/SQL使用SQL语句查询 点击上方导航栏,New,选择SQL Window,即可再次输入要查询的

    2024年02月19日
    浏览(68)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包