postgresql – postgres层次结构 – 子级别的计数,按子项或孙子

发布时间：2020-12-13 15:52:49 所属栏目：百科来源：网络整理

导读：我想知道如何编写postgres子查询,以便下表示例将输出我需要的内容. id parent_id postdate1 -1 2015-03-10 2 1 2015-03-11 (child level 1)3 1 2015-03-12 (child level 1)4 3 2015-03-13 (child level 2)5 -1 2015-03-146 -1 2015-03-157 6 2015-03-16 (chi

我想知道如何编写postgres子查询,以便下表示例将输出我需要的内容.

id   parent_id    postdate

1    　　-1      2015-03-10 
2 　　　　1      2015-03-11  (child level 1)
3　　　　 1      2015-03-12  (child level 1)
4　　　　 3      2015-03-13  (child level 2)
5　　　　-1      2015-03-14
6　　　　-1      2015-03-15
7　　　　 6      2015-03-16  (child level 1)

如果我想按子级别1对所有根id进行排序,并且父级的子级数是,那么输出将是这样的

id    count　     date
6　　　2　　　  2015-03-15
1　　　4　　　  2015-03-10
5　　　1　　　  2015-03-14

输出根据root的子进程按postdate排序.输出的“日期”是根的过期日期.即使id#5有更新的postdate,rootid#6的孩子(id#7)也有最新的postdate,因为它是按孩子的postdate排序的. id#5没有任何孩子所以它只是放在最后,按日期排序. ‘count’是孩子(孩子1级),孙子(孩子2级)和自己(root)的数字.例如,id#2,#3,#4都属于id#1,因此对于id#1,计数将为4.

我目前的子查询到目前为止：

SELECT p1.id,count(p1.id),p1.postdate
 FROM mytable p1
     LEFT JOIN mytable c1 ON c1.parent_id = p1.id AND p1.parent_id = -1
     LEFT JOIN mytable c2 ON c2.parent_id = c1.id AND p1.parent_id = -1
GROUP BY p1.id,c1.postdate,p1.postdate
ORDER by c1.postdate DESC,p1.postdate DESC

解决方法

create table mytable ( id serial primary key,parent_id int references mytable,postdate date );
create index mytable_parent_id_idx on mytable (parent_id);
insert into mytable (id,parent_id,postdate) values (1,null,'2015-03-10');
insert into mytable (id,postdate) values (2,1,'2015-03-11');
insert into mytable (id,postdate) values (3,'2015-03-12');
insert into mytable (id,postdate) values (4,3,'2015-03-13');
insert into mytable (id,postdate) values (5,'2015-03-14');
insert into mytable (id,postdate) values (6,'2015-03-15');
insert into mytable (id,postdate) values (7,6,'2015-03-16');

with recursive recu as (
  select id as parent,id as root,null::date as child_postdate
    from mytable
   where parent_id is null
   union all
  select r.parent,mytable.id,mytable.postdate
    from recu r
    join mytable
      on parent_id = r.root
)
select m.id,c.cnt,m.postdate,c.max_child_date
  from mytable m
  join ( select parent,count(*) as cnt,max(child_postdate) as max_child_date
           from recu
          group by parent
       ) c on c.parent = m.id
 order by c.max_child_date desc nulls last,m.postdate desc;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!