准备结构

  1. create  table t5( id bigserial primary key , parent_id bigint not null ,title text,orderby bigint default 0);
  3. create or replace function t5_add(v_parent_id bigint) returns text language plpgsql as $$
  4.     declare
  5.         v_id bigint = nextval('t5_id_seq');
  6.         v_parent_title text;
  7.         v_count bigint;
  8.     begin
  9.         if(v_parent_id=0)
  10.         then
  11.             insert into t5 values  (v_id,0,v_id);
  12.         else
  13.             v_count =  (select count(1)+1 from t5 where parent_id =v_parent_id);
  14.             v_parent_title :=  (select title from t5 where id = v_parent_id);
  15.             insert into t5 values (v_id,v_parent_id,concat(v_parent_title,'-',v_count));
  16.         end if;
  17.         return v_id::text;
  18.     end
  19. $$;

准备数据

INSERT INTO public.t5 (id, parent_id, title, orderby) VALUES (1, 0, '1', 0);
INSERT INTO public.t5 (id, parent_id, title, orderby) VALUES (2, 0, '2', 0);
INSERT INTO public.t5 (id, parent_id, title, orderby) VALUES (3, 0, '3', 0);
INSERT INTO public.t5 (id, parent_id, title, orderby) VALUES (4, 1, '1-1', 0);
INSERT INTO public.t5 (id, parent_id, title, orderby) VALUES (6, 1, '1-3', 0);
INSERT INTO public.t5 (id, parent_id, title, orderby) VALUES (7, 2, '2-1', 0);
INSERT INTO public.t5 (id, parent_id, title, orderby) VALUES (8, 2, '2-2', 0);
INSERT INTO public.t5 (id, parent_id, title, orderby) VALUES (9, 2, '2-3', 0);
INSERT INTO public.t5 (id, parent_id, title, orderby) VALUES (10, 6, '1-3-1', 0);
INSERT INTO public.t5 (id, parent_id, title, orderby) VALUES (11, 6, '1-3-2', 0);
INSERT INTO public.t5 (id, parent_id, title, orderby) VALUES (12, 6, '1-3-3', 0);
INSERT INTO public.t5 (id, parent_id, title, orderby) VALUES (13, 9, '2-3-1', 0);
INSERT INTO public.t5 (id, parent_id, title, orderby) VALUES (14, 9, '2-3-2', 0);
INSERT INTO public.t5 (id, parent_id, title, orderby) VALUES (15, 9, '2-3-3', 0);
INSERT INTO public.t5 (id, parent_id, title, orderby) VALUES (16, 4, '1-1-1', 0);
INSERT INTO public.t5 (id, parent_id, title, orderby) VALUES (17, 4, '1-1-2', 0);
INSERT INTO public.t5 (id, parent_id, title, orderby) VALUES (18, 4, '1-1-3', 0);
INSERT INTO public.t5 (id, parent_id, title, orderby) VALUES (19, 5, '1-2-1', 0);
INSERT INTO public.t5 (id, parent_id, title, orderby) VALUES (21, 5, '1-2-2', 0);
INSERT INTO public.t5 (id, parent_id, title, orderby) VALUES (5, 1, '1-2', 2);

create or replace function t5_add(v_parent_id bigint) returns text language plpgsql as $$
    declare
        v_id bigint = nextval('t5_id_seq');
        v_parent_title text;
        v_count bigint;
    begin
        if(v_parent_id=0)
        then
            insert into t5 values  (v_id,0,v_id);
        else
            v_count =  (select count(1)+1 from t5 where parent_id =v_parent_id);
            v_parent_title :=  (select title from t5 where id = v_parent_id);
            insert into t5 values (v_id,v_parent_id,concat(v_parent_title,'-',v_count));
        end if;
        return v_id::text;
    end
$$;

数据

image.png

要求

image.png

说明

  1. 大体顺序是按id正序排
  2. 从第一条记录开始 如果存在后续节点 则优先输出所有后代节点, 再输出后续同级节点
  3. 可以按照另一个字段对同一级别的元素排序(如上图name为1-2的记录, 对比name为1-1的记录

查询


with recursive tree (id, name, parent_id, orderby, sortable) as (
    select id,title,parent_id,orderby, array [-orderby,id] as sortable
    from t5
    where parent_id = 0
    union all
    -- 核心在于sortable 连结上下级的
    select t2.id,t2.title,t2.parent_id,t2.orderby, tree.sortable || -t2.orderby || t2.id
    from t5 t2
    join tree on tree.id = t2.parent_id
)select * from tree order by sortable;