只保留第一列的第一个值，并根据层次结构对另外两个列进行排序。

Question

所以有五个人在笑工厂工作。马克先起来，然后是朱迪，然后是洛丽，然后是艾格尼丝，最后是以赛亚。

我想从这一点出发：

+------------------------------+
| Laugh Factory |Marc | Judy   |
| Laugh Factory |Judy | Lorie  |
| Laugh Factory |Lorie| Agnes  |
| Laugh Factory |Agnes| Isaiah |
+------------------------------+

对此：

 Laugh Factory | Marc | Judy | Lorie | Agnes | Isaiah

我怎样才能做到这一点？

Answer 1

为此您需要一个递归查询。

类似的东西(未经测试的)：

with recursive tree as (
  select company, comedian, preceding_comedian, 1 as level
  from the_table
  where company = 'Laugh Factory'
    and preceding_comedian is null
  union all
  select ch.company, ch.comedian, ch.preceding_comedian, p.level + 1
  from the_table ch
    join tree p on ch.preceding_comedian = p.comedian
)
select company, string_agg(comedian, ' > ' order by level) as comedians
from tree 
group by company;