美文网首页
postgresql递归查询总结

postgresql递归查询总结

作者: 朱传武 | 来源:发表于2020-06-18 21:59 被阅读0次

背景

由于业务需要,app_labels表存储了所有专题以及content的树结构,而且这个树结构是不确定深度的,某些场景我们面临着根据某个item来获取父、子item,这种情况显然一个简单的关联关系就能搞定,今天遇到的问题是要取某个item的所有父级,最初想法是把app_labels表里面所有数据返回前端,前端做递归处理,这样做显然不太合理,其一,数据量太庞大,其二,这个表刷新非常频繁,前端也要实时刷新数据,代价有点高……,经一番查询,发现sql也可以做递归。

app_labels表结构

jVKIfW.jpg

其中关键是label_sign以及superlabel_sign来记录树关系,测试数据如下:

CREATE TABLE testapp_db.app_labels
(
    label_id bigint NOT NULL DEFAULT nextval('testapp_db.app_labels_label_id_seq'::regclass),
    label_sign character varying(100) COLLATE pg_catalog."default" NOT NULL,
    label_fullname text COLLATE pg_catalog."default",
    superlabel_sign character varying(100) COLLATE pg_catalog."default",
    label_order integer DEFAULT 0,
    delete_flag boolean NOT NULL DEFAULT false,
    create_time timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT app_label_pkey PRIMARY KEY (label_id),
    CONSTRAINT label_order_is_unique UNIQUE (label_order, superlabel_sign),
    CONSTRAINT label_sign_is_unique UNIQUE (label_sign),
    CONSTRAINT superlabel_sign_fkey FOREIGN KEY (superlabel_sign)
        REFERENCES testapp_db.app_labels (label_sign) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

插入测试数据如下:

insert into app_labels
(label_sign, label_fullname, superlabel_sign, label_order)
VALUES
('sales', 'subject_salses', 'course', '0'),
('s-c1', 'subject_sales-category_1', 'sales', '1'), 
('s-c2', 'subject_sales-category_2', 'sales', '2'), 
('s-c3', 'subject_sales-category_3', 'sales', '3'),
('s1', 'content_pss-1', NULL, 0),
('s1-1', 'content_pss-1-chapter_1', 's1', '1'),
('s1-1.1', 'content_pss-1-chapter_1.1', 's1-1', '1'),
('s1-1.2', 'content_pss-1-chapter_1.2', 's1-1', '2'),
('s1-1.3', 'content_pss-1-chapter_1.3', 's1-1', '3'),
('s1-2', 'content_pss-1-chapter_2', 's1', '2'),
('s1-2.1', 'content_pss-1-chapter_2.1', 's1-2', '1'),
('s1-2.2', 'content_pss-1-chapter_2.2', 's1-2', '2'),
('s1-2.3', 'content_pss-1-chapter_2.3', 's1-2', '3'),
('s1-3', 'content_pss-1-chapter_3', 's1', '3'),
('s1-3.1', 'content_pss-1-chapter_3.1', 's1-3', '1'),
('s1-3.2', 'content_pss-1-chapter_3.2', 's1-3', '2'),
('s2', 'content_pss-2', NULL, 0),
('s2-1', 'content_pss-2-chapter_1', 's2', '1'),
('s2-1.1', 'content_pss-2-chapter_1.1', 's2-1', '1'),
('s2-1.2', 'content_pss-2-chapter_1.2', 's2-1', '2'),
('s2-2', 'content_pss-2-chapter_2', 's2', '2'),
('s2-2.1', 'content_pss-2-chapter_2.1', 's2-2', '1'),
('s2-2.2', 'content_pss-2-chapter_2.2', 's2-2', '2'),
('s2-2.3', 'content_pss-2-chapter_2.3', 's2-2', '3'),
('s3', 'content_pss-3', NULL, 0),
('s3-1', 'content_pss-3-chapter_1', 's3', 1),
('s3-1.1', 'content_pss-3-chapter_1.1', 's3-1', 1),
('s3-1.2', 'content_pss-3-chapter_1.2', 's3-1', 2),
('s3-1.3', 'content_pss-3-chapter_1.3', 's3-1', 3),
('s3-2', 'content_pss-3-chapter_2', 's3', 2),
('s3-2.1', 'content_pss-3-chapter_2.1', 's3-2', 1),
('s3-2.1.1', 'content_pss-3-chapter_2.1.1', 's3-2.1', 1),
('s3-2.1.2', 'content_pss-3-chapter_2.1.2', 's3-2.1', 2),
('s3-2.2', 'content_pss-3-chapter_2.2', 's3-2', 2),
('s4', 'content_pss-4', NULL, 0),
('s4-1', 'content_pss-4-chapter_1', 's4', 0),
('s4-1.1', 'content_pss-4-chapter_1.1', 's4-1', 1),
('s4-1.2', 'content_pss-4-chapter_1.2', 's4-1', 2)
;

查询item所有父标签

postgresql关于递归的官方文档

尝试编写递归sql语句

WITH RECURSIVE tmp AS ( 
       SELECT * FROM app_labels WHERE label_sign = 's3-2.1' 
     union   ALL 
       SELECT app_labels.* FROM app_labels, tmp WHERE app_labels.label_sign = tmp.superlabel_sign 
     ) 
 SELECT * FROM tmp;

返回结果如下:

LpxteR.jpg

貌似成功了,说明递归管用,下一步我们要把它转换成客户端可以访问的接口形式:

CREATE or replace FUNCTION func_get_parent1(in in_id varchar, out o_area text)  AS 

$$ 
DECLARE
   v_rec_record RECORD;
BEGIN

  o_area = '';
  FOR v_rec_record IN (WITH RECURSIVE tmp AS (SELECT *
                           FROM testapp_db.app_labels
                          WHERE label_sign = in_id
                         union ALL
                         SELECT testapp_db.app_labels.*
                           FROM testapp_db.app_labels, tmp
                          WHERE testapp_db.app_labels.label_sign = tmp.superlabel_sign)SELECT name
                         FROM tmp
                        ORDER BY id) LOOP
    o_area := o_area || '~' || v_rec_record.label_sign;
  END LOOP;
  return;
END; 
$$
LANGUAGE 'plpgsql';

查看是否执行起效:

select func_get_parent1('s3-2.1') ;

结果如下


image-20200617230654064.png

所有父节点都出来了 ,这里我只需要父节点的label即可,若有其他需要,可修改sql语句,客户端请求:

T4Q1Wo.jpg

成功啦!!

查询item所有子标签

照葫芦画瓢把递归语句稍微改下即可:

WITH RECURSIVE tmp AS ( 
       SELECT * FROM app_labels WHERE label_sign = 's3-2.1' 
     union   ALL 
       SELECT app_labels.* FROM app_labels, tmp WHERE app_labels.superlabel_sign = tmp.label_sign 
     ) 
 SELECT * FROM tmp;

自己试试去吧!

相关文章

  • postgresql递归查询总结

    背景 由于业务需要,app_labels表存储了所有专题以及content的树结构,而且这个树结构是不确定深度的,...

  • postgresql可以递归查询么

    PostgreSQL提供了WITH语句,允许你构造用于查询的辅助语句。这些语句通常称为公共表表达式或cte。cte...

  • postgresql递归查询转JSON

    1、创建数据表 2、插入记录 3、递归查询语句 运行结果如下: 数据量较大的情况下,递归查询较慢,所以要引入物化视...

  • Postgresql实现递归查询字典并转JSON

    现在有一张字典表,有二级 、三级字典项,需要递归查询并转换成JSON对象。字典数据情况如下: 查询语句如下: 查询...

  • SQL 日常用法积累

    自定义排序 多字段模糊查询 排序对空值的处理 postgresql jsonb数据查询 postgresql

  • postgresql 表的列名,数据库表名

    postgresql 查询某一个表中的所有字段,也就是查询所有的列名 postgresql 查询数据库表名

  • DNS解析原理:递归 VS 迭代

    DNS 解析分类 DNS解析流程分为递归查询和迭代查询,递归查询是以本地名称服务器为中心查询, 递归查询是默认方式...

  • Java工程师之Oracle技术-SQL入门(6)

    递归查询 递归查询 存在层级关系(树形)关系时,使用SQL语句可以把整个递归树全部查询出来 level 关键字可以...

  • 数据库篇

    2018.07.04 postgresql查询表的大小 数据库中单个表的大小(不包括索引) postgresql的...

  • Linux命令--grep

    参数说明 -n 展示行号-r 递归查询 组合使用 grep -rn "content" ./ 在当前目录下递归查询...

网友评论

      本文标题:postgresql递归查询总结

      本文链接:https://www.haomeiwen.com/subject/nezexktx.html