SQL-POSTGRESQL:根据一定的条件对表进行排序

Question

所以我有这个表

person_id    child      mom 
1            john       marry
2            mark       sophia
3            emma       null
4            alfonso    marry
5            sophia     null
6            marry      isabella
7            michael    emma
8            isabella   null

我想把这张桌子分类，让每个妈妈都有自己的孩子。例如

person_id    child       mom 
1            isabella    null
2            marry       isabella
3            john        marry
4            alfonso     marry
5            sophia      null
6            mark        sophia
7            emma        null
8            michael     emma

注意：母亲既可以是母亲也可以是孩子例如：已婚有两个孩子(约翰和阿方索(，但她也是伊莎贝拉的女儿

有什么方法可以在sql中实现这一点吗？

Answer 1

如果我正确地跟踪您，您需要一个递归查询：

with recursive cte as (
select t.*, array[person_id] path from mytable t where mom is null
union all
select t.*, c.path || t.person_id
from cte c
inner join mytable t on t.mom = c.child
)
select * from cte order by path

其想法是构建每一行的路径，然后可以使用该路径对结果集进行排序。

DB Fiddle上的演示：

person_id|child|mom|path--------：|：-------|：-------|:------3|emm|null|{3}7 |迈克尔|埃玛|｛3,7｝5|sofia|null|{5}2|mark|sofia|{5,2}8|isabella|null|{8}6|结婚|isabella|{8,6}1|约翰|结婚|{8,6,1}4|alfonso|mare|{8,6,4}