SQL选择与根父

Question

我有一个表Members(id, name, parent_id)，其中parent_id是成员的父(它也是一个成员，可以有它的父)。例如

id | name | parent_id
----------------------
1  | John | NULL
2  | Smith| 1
3  | Andy | 1
4  | Joe  | 2
5  | Rick | 2
6  | Craig| 5
7  | Greg | NULL
8  | Bob  | 5
9  | Mike | 8 

我想运行语句select from members，我想要有

id | name | parent_id | root_parent_id
--------------------------------------
1  | John | NULL      | NULL
2  | Smith| 1         | 1
3  | Andy | 1         | 1
4  | Joe  | 2         | 1
5  | Rick | 2         | 1
6  | Craig| 5         | 1
7  | Greg | NULL      | NULL
8  | Bob  | 7         | 7
9  | Mike | 8         | 7

我想尽可能深入地找到所有成员的root_parent_id。

Answer 1

with recursive recursive_members as (
    select *, id root_id, 1 depth
    from members
union all
    select r.id, r.name, r.parent_id, m.parent_id, r.depth+ 1
    from recursive_members r
    join members m on r.root_id = m.id
    where m.parent_id notnull
)
select distinct on (id) *
from recursive_members
order by id, depth desc;
 id | name  | parent_id | root_id | depth 
----+-------+-----------+---------+-------
  1 | John  |           |       1 |     1
  2 | Smith |         1 |       1 |     2
  3 | Andy  |         1 |       1 |     2
  4 | Joe   |         2 |       1 |     3
  5 | Rick  |         2 |       1 |     3
  6 | Craig |         5 |       1 |     4
  7 | Greg  |           |       7 |     1
  8 | Bob   |         5 |       1 |     4
  9 | Mike  |         8 |       1 |     5
(9 rows)

阅读递归WITH查询