我想知道如何编写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;
